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:
CALCULATE(SalesAndCosts[Margin],PREVIOUSQUARTER(Calendar[Date]))
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]
, 1,
-1)
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.
