Forum Discussion
Helper IIIDAX: Calculate percentage difference between two measures
I have a chart with actual values (KPIact) and plan values (KPIplan).
I want to change the color of the charts based on KPIact.
For example: When KPIact < KPIplan, then I want to show red columns.
I was already able to do that, but I also want that when the difference between those two values is under 2% then it should be orange, but somehow this does not work..
This should do it...
IF(AND('KPI Data'[KPIact]<'KPI Data'[KPIplan], ('KPI Data'[KPIplan]-'KPI Data'[KPIact])/'KPI Data'[KPIplan]*100 < 2), "#FFBF00", IF('KPI Data'[KPIact] <= 'KPI Data'[KPIact], "#D2222D", "#238823")
5 Replies
jgeddesSuper User
So you are looking for:
Actuals less than Planned Value = Red
Actuals within 2% of Planned Value = Yellow
Actuals greater than Planned Value = Green?
tuncayActuals less than planned value and difference bigger than 2% = Red
Actuals within 2% of Planned Value AND below Planned Value = Yellow
Actuals greather than Planned Value = Green
- jgeddes
This should do it...
IF(AND('KPI Data'[KPIact]<'KPI Data'[KPIplan], ('KPI Data'[KPIplan]-'KPI Data'[KPIact])/'KPI Data'[KPIplan]*100 < 2), "#FFBF00", IF('KPI Data'[KPIact] <= 'KPI Data'[KPIact], "#D2222D", "#238823")
- jgeddes
I would try changing the order of your arguements in your IF statement so the test for <2% is first.
- tuncay
then also values above KPIplan get also orange (and not green) even though they are higher..