I have a requirement where I need to do variance analysis on stages of data received.
| Dimension1 | Dimension2 | Stage | Value |
| D1 | D2 | S1 | 4320.6 |
| D1 | D2 | S2 | 1680.41 |
| D1 | D2 | S3 | 2386.55 |
| D1 | D2 | S4 | 2828.67 |
| D1 | D2 | S5 | 2828.67 |
| D1 | D3 | S1 | 1643.43 |
| D1 | D3 | S2 | 1643.43 |
| D1 | D3 | S3 | 1700 |
| D1 | D3 | S4 | 1700 |
| D1 | D3 | S5 | 1700 |
By default there would be just 5 stages S1, S2, S3, S4 and S5.
So my resulting pivot chart would be:
| Dimension1 | Dimension2 | S1 | S2 | S3 | S4 | S5 |
| D1 | D2 | 4320.6 | 1680.41 | 2386.55 | 2828.67 | 2828.67 |
| D1 | D3 | 1643.43 | 1643.43 | 1700 | 1700 | 1700 |
Here the values in S2 – S5 are color coded depending on it being greater than, less than or equal to the previous value. E.g., S2 in the first row if less that S1 in the same row, hence S2 value is red.
If there is an increase in the value then it is orange and if it’s the same then its green.
I am calculating the S1-S2 values using set analysis, hence the stages are hard coded in the expressions:
Following is the expression I have for S2. Also have an expression on the Text color of the expression to show it as Red, Green or Orange.
=IF(Sum({$<Stage={'S1'}>}Value) > Sum({$<Stage={'S2'}>}Value)
,' ▼ '
,IF(Sum({$<Stage={'S1'}>}Value) < Sum({$<Stage={'S2'}>}Value)
,' ▲ ',' '
)
) & ' ' & Sum({$<Stage={'S2'}>}Value)
Here is my difficulty, there is a possibility that sometime there may be an intermediate stage say S2-1 which should appear between S2 and S3 and variance calculated. I would like to make it dynamic and also show the conditional formatting of the values.
What is the best way to implement this? I can use a normal pivot with the Stage as a dimension and then transpose the dimension to show as columns. But doing this how do I also apply the formatting?
Appreciate your help on this.
