Forum Discussion
Conditional Formatting for Column Color based on different KPIs
I try to conditional format different sets of data simultaneously through a bar chart that navigates through different KPIs through a slicer, I want the column to be colored green if the value is greater than or equal to the target value for example, I have listed the conditions below:
- MC Eff: green if greater than or equal target , red otherwise
- Weigher Eff: green if greater than or equal target , red otherwise
- GGA: green if less than or equal target , red otherwise
- Film Waste: green if less than or equal target , red otherwise
- R&M and Operating Supplies: No formatting ( stay default orange color)
I have tried making a nested IF function and placed into a measure and then corresponding the output of the measure to the conditional formatting in the visual formatting as attached in the pics below but there's some kind of conflict that occurs that makes all the colors interfere with each other. The code i used is also pasted below:
I also tried to modify the code to this but also no use:
Hi Anonymous
I've written this so you can make them all seperate tests if you want to using the individual switches.
Color Change MC Eff. =
VAR currMeasure =
SELECTEDVALUE ( 'KPI'[KPI] )
RETURN
SWITCH (
currMeasure,
"MC Efficiency (%)", SWITCH ( TRUE (), [MC Efficiency (%)] > [MC Efficiency T], "Green" ,"Red"),
"Weigher Efficiency (%)", SWITCH ( TRUE (), [Weigher Efficiency (%)] > [Weigher Efficiency T], "Green","Red" ),
"GGA", SWITCH ( TRUE (), [GGA (gm)] < [GGA T], "Green","Red" ),
"Film Waste (%)", SWITCH ( TRUE (), [Film Waste (%)] < 'Film Waste (%)'[Film Waste T], "Green","Red" ),
"R&M", "orange",
"Operating Supplies", "orange",
"Red" --If anything else
)
6 Replies
- SamWiseOwl
Super User
Hi Anonymous
I've written this so you can make them all seperate tests if you want to using the individual switches.
Color Change MC Eff. =
VAR currMeasure =
SELECTEDVALUE ( 'KPI'[KPI] )
RETURN
SWITCH (
currMeasure,
"MC Efficiency (%)", SWITCH ( TRUE (), [MC Efficiency (%)] > [MC Efficiency T], "Green" ,"Red"),
"Weigher Efficiency (%)", SWITCH ( TRUE (), [Weigher Efficiency (%)] > [Weigher Efficiency T], "Green","Red" ),
"GGA", SWITCH ( TRUE (), [GGA (gm)] < [GGA T], "Green","Red" ),
"Film Waste (%)", SWITCH ( TRUE (), [Film Waste (%)] < 'Film Waste (%)'[Film Waste T], "Green","Red" ),
"R&M", "orange",
"Operating Supplies", "orange",
"Red" --If anything else
) thanks Sam for your reply, however I pasted the code u provided and still there's some deviation from what's intended.
The GGA are all colored red and I modified the ">" to ">=" and still the values equal to the target are colored red (should be green)
I attached the file for u to review on google drive:
https://drive.google.com/drive/folders/13HYEo7dusjcHUzv7tT8pq5YXHcWR2d9G?usp=sharing- SamWiseOwl
Hi Anonymous
The GGM needs to be GGM (gm) to match what it is in the slicer:
SamWiseOwl It turned out to be rounding figures as u mentioned, thanks a lot for the support