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.
2 comments:
Hi!
I think the trend is a "must have" in KPIs.
It works fine for me, but it has an inconvenient. If I want to visualize only a period (filtering powerpivot), for example a month; then the whole data is filtered and the trend against previous month can't be calculated in the DAX formula.
Is there any workaround for this situation?
Thanks!
Carles,
Thanks for this feedback.
However the calculation is right. Whith CALCULATE we apply a new filter context, so overwriting the existing filter (eg. one period)
If you remove "Show icon only" from the condiditional formatting you will see that it evaluates correctly to -1 or 1.
The problem lies in the conditial formatting. You need to apply the following setting in the conditional formatting.
Check the checkbox Show Icon Only.
Green when value is > 0 (Number)
Amber when <0= and >= 0 (Number)
Red when < 0
Thanks for you comment, I fixed the screenshot and updated the blogpost.
Post a Comment