Forum Discussion
conditional formatting colors when comparing values
I'm formatting colums to change colors when compared to a planned value. If it's lower it turns red, if it's higher it turns green.
When the cells are empty the default to not being less that the planned value and are green.
Is there a way to default to white when no data is entered? This is the DAX I'm using:
Color Mar = IF ( MAX ( 'KPI' [Mar-Actual] ) < MAX ( 'KPI' [Mar-Plan] ) , "#BBF4C6", "#E68F96" )
With the help of MageVortex this is the DAX that worked:
Color March = IF ( COUNT ( 'KPI' [Actual] ) = BLANK(), "#FFFFFF", IF ( MAX ( 'KPI' [Actual] ) < MAX ( 'KPI' [Plan] ), "#E68F96", "#BBF4C6" ))
You have to use the hex code for the colors, I made the mistake of not entering a hashtag before the color code and it created an error. Thanks for your help MageVortex.
9 Replies
With the help of MageVortex this is the DAX that worked:
Color March = IF ( COUNT ( 'KPI' [Actual] ) = BLANK(), "#FFFFFF", IF ( MAX ( 'KPI' [Actual] ) < MAX ( 'KPI' [Plan] ), "#E68F96", "#BBF4C6" ))
You have to use the hex code for the colors, I made the mistake of not entering a hashtag before the color code and it created an error. Thanks for your help MageVortex.
Can you do a nested if?
Nested Color Mar = IF ( 'KPI' [Mar-Actual] = "000000", IF ( MAX ( 'KPI' [Mar-Actual] ) < MAX ( 'KPI' [Mar-Plan] ) , "#BBF4C6", "#E68F96" )
Granted, 000000 may be the wrong value to use. Just a thought, let me know if it fails 🙂
It's giving me an error message, it's asking to specify an aggregation, min, max, count, sum
Sorry, I forgot to finish the equation, it should have read more akin to:
IF ( 'KPI' [Mar-Actual] =BLANK(), "000000", IF (blahblahblahOr perhaps a
0
in place of
BLANK()That may not help the report I was testing with is my own and set up differently than yours. If you must use min max or sum, you can just try sum = 0 or something like that.
Hope that helps.
I got it to work!!!
Please do tell us how you got it to work and mark it as an answer to future peep's can take advantage of your genius : )
Color mar = IF ( COUNT ( 'KPI' [Mar-Actual] ) = BLANK(), "#FFFFFF", IF ( MAX ( 'KPI' [Mar-Actual] ) < MAX ( 'KPI' [Mar-Plan] ), "#BBF4C6" , "#E68F96"))
