Creating Story-Defined Thresholds
Thresholds can be used to compare measures to a fixed range or to other measures.
Prerequisites
To create a threshold, you must be on a story page.
- SAP Business Warehouse (BW) connections
- SAP S/4HANA connections
- Out of Context Blending (creating blended charts or tables without including the linked dimension in the chart or table)
A threshold is defined on a specific hierarchy in an account dimension. When you have multiple hierarchies in an account dimension, you will need to create different thresholds for each hierarchy.
Context
Use thresholds to provide visual cues for your information, so that you can quickly see what areas are doing well, and what areas may need improvements.
When you are comparing negative values, your results may not be what you assumed they should be.
To provide more accurate results, the system uses a formula to compare measures:
if (MeasureB == 0) { \\(Can't divide by 0)
MeasureA/|MeasureA| > percentage \\(absolute value of MeasureA)
} else if (MeasureB < 0 || MeasureA < 0){
(MeasureA - MeasureB)/|MeasureB| > percentage \\(compare variance to percentage)
} else {
MeasureA/MeasureB > percentage \\(compare ratio to percentage)
}
The following table shows how the formula is applied.
| Condition | Formula | Description |
|---|---|---|
| If MeasureB is 0 | MeasureA / ABS(MeasureA) | Return +/- 100% when baseline is 0 |
| If MeasureA and MeasureB are positive (>0) | MeasureA / MeasureB | Ratio |
| If MeasureA or MeasureB is negative, or MeasureA is 0 | MeasureA - MeasureB / ABS(MeasureB) | Variance |
If you put that formula into Excel, you would write it as follows: =IF(B5=0,A5/ABS(A5), IF(OR(A5<0,B5<0),((A5-B5)/ABS(B5)), A5/B5))
The following table shows the ratio and formula results from comparing the positive and negative values (profits/losses) for two years.
| A | B | C | D | E | |
| 1 | This year (A) | Last year (B) | Ratio result (A/B) | Formula result | Description |
| 2 | 1,000,000 | 2,000,000 | 50% | 50% | Company made half as much this year. |
| 3 | 2,000,000 | 1,000,000 | 200% | 200% | Company doubled profits. |
| 4 | 0 | 1,000,000 | 0% | 0% | Company made no money this year after a profit last year. |
| 5 | -1,000,000 | 0 | #DIV/0! | -100% | Company lost money this year after breaking even last year. |
| 6 | -1,000,000 | -2,000,000 | 50% | 50% | Company lost half as much this year. |
| 7 | -2,000,000 | -1,000,000 | 200% | -100% | Company lost twice as much this year. |
| 8 | -1,000,000 | 1,000,000 | -100% | -200% | Company turned a profit into a loss. |
| 9 | 1,000,000 | -1,000,000 | -100% | 200% | Company turned a loss into a profit. |
- Green square: OK
- Orange triangle: Warning
- Red circle: Critical
When the Thresholds panel opens, you may only see one range: a green square followed by the word OK. Click Add Range twice to add the Warning and Critical ranges.
You can use as many or as few ranges as you like, and you can change the label names and colors. (However, when you change the icon colors, you may see a circle with your new color, not a triangle or square.)
When you enter the value for your range, the value appears on the line at the bottom of the panel. The value includes a letter to designate whether it is thousands, millions, and so on.
You do not need to set both an upper and a lower bound if you have only one range. When you add more ranges, you can leave either the upper or lower bound empty.
Procedure
-
From the Tools menu, select
(Conditional Formatting).