0 Replies Latest reply: Jan 19, 2017 4:32 PM by John Henry RSS

    How to change Conditional Colors of a KPI based on filter selections?

    John Henry

      I have a dataset that has sales by month, however, the budget is by quarter (not month).  I've created a KPI that calculates the % of budget (attainment) for a given quarter.  I would like to set a condition where the KPI changes color based on the % attainment value. 

       

      For example, assume a calendar year, and we have just completed the month of Jan, and the quarterly budget for Q1 is $100. Assuming that sales are $20 in Jan, my % of budget would be 20% ($20/$100) for QTD performance.  As a benchmark, I would want the sales for Jan to be at least 33% (1 out of 3 months) of my Q1 budget...this is my condition for coloring my KPI.  In this case, the KPI would turn RED because it is less than 33%.

       

      Now fast forward to Feb and pretend that sales were $50. QTD sales for Q1 would now be $70 ($50 Feb+ $20 Jan) .  Since my target attainment was 33% for Jan, I will apply the same target to Feb so my total % of budget target should be at least 67% (Jan 33%+Feb 33%).  Since $70 is 70% of my Q1 budget, I want my conditional coloring to revert to GREEN since it is greater than my assumed target of 67%.

       

      Here are my specific conditions for any qiven quarter:

      1. If month 1 (M1) sales of a given quarter are equal to or greater than 33% of budget, then green, otherwise, red
      2. If M1+M2 sales of a given quarter are equal to or greater than 67% of budget, then green, otherwise, red
      3. If M1+M2+M3 sales of a given quarter are equal to or greater than 100% of budget, then green, otherwise, red