Twitter Updates

    follow me on Twitter

    Friday, March 30, 2012

    PowerPivot Nuggets - Part 17 - Visualize KPI Trends

    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]
    , 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.


    Carles Royan said...


    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?


    Frederik Vandeputte said...


    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.