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:
- If month 1 (M1) sales of a given quarter are equal to or greater than 33% of budget, then green, otherwise, red
- If M1+M2 sales of a given quarter are equal to or greater than 67% of budget, then green, otherwise, red
- If M1+M2+M3 sales of a given quarter are equal to or greater than 100% of budget, then green, otherwise, red
