KPIs are a nice new feature in PowerPivot 2012. I blogged about KPIs before. In PowerPivot KPIs have a value, a target and a status. Compared to KPIs in full blown Analysis Services OLAP cubes we are missing the possibility to visualize the trend of the KPI.
In this nugget I will show you a trick how you can visualize the trend of a KPI. Let's have a look at this example.
First of all we need to find the value of the Margin KPI for the previous quarter. This pretty straightforward with the following calculated measure using the CALCULATE function:
We calculate the margin, and change the filter context to the previous quarter.
Next we can check if this value is lower than the margin for the current month. If this is the case we return 1, otherwise we return -1.
IF(CALCULATE(SalesAndCosts[Margin],PREVIOUSQUARTER(Calendar[Date])) < SalesAndCosts[Margin]
This will result in the following pivot table with 1s and -1s.
By using conditional formatting we can make the table visually more attractive. Choose Conditional Formatting from the Home ribbon, Icon Sets, 3 arrows.
Next, choose Conditional Formatting again, click Manage Rule, Edit Rules and change the following parameters:
- Check the checkbox Show Icon Only.
- Green when value is > 0 (Number)
- Amber when <0= and >= 0 (Number)
- Red when < 0
This the final result.
This workaround works pretty fine, disadvantage is the fact that the icon style is not saved with the KPI. You need to repeat the conditional formatting on every new pivot table.