Conditional Formatting for Column Color based on different KPIs

[AhmedMedhat]

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:

Spoiler

Color Change MC Eff. = IF('MC Efficiency (%)'[MC Efficiency (%)]>'MC Efficiency (%)'[MC Efficiency T],"green",IF('Weigher Efficiency (%)'[Weigher Efficiency (%)]>'Weigher Efficiency (%)'[Weigher Efficiency T],"green",IF('GGA (gm)'[GGA (gm)]<'GGA (gm)'[GGA T],"green",IF('Film Waste (%)'[Film Waste (%)]<'Film Waste (%)'[Film Waste T],"green",IF(OR('Operating Supplies'[Operating Supplies Measure],'R&M'[R&M]),"orange","red")))))

I also tried to modify the code to this but also no use:

Spoiler

Color Change MC Eff. = IF(

    SELECTEDVALUE('KPI'[KPI]) = "MC Efficiency (%)" &&

     'MC Efficiency (%)'[MC Efficiency (%)]>='MC Efficiency (%)'[MC Efficiency T],"green",

     IF(SELECTEDVALUE('KPI'[KPI]) = "Weigher Efficiency (%)" &&

     'Weigher Efficiency (%)'[Weigher Efficiency (%)]>='Weigher Efficiency (%)'[Weigher Efficiency T],"green",IF(SELECTEDVALUE('KPI'[KPI])="GGA" &&

     'GGA (gm)'[GGA (gm)]<='GGA (gm)'[GGA T] ,"green",

     IF( SELECTEDVALUE('KPI'[KPI]) = "Film Waste (%)" &&

     'Film Waste (%)'[Film Waste (%)] <= 'Film Waste (%)'[Film Waste T], "green",

     IF(OR(SELECTEDVALUE('KPI'[KPI]) ="R&M", SELECTEDVALUE('KPI'[KPI])="Operating Supplies"),"orange","red")

     ))))

Please advise on how I can resolve this problem

[AhmedMedhat]

@SamWiseOwl It turned out to be rounding figures as u mentioned, thanks a lot for the support

[AhmedMedhat]

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 @AhmedMedhat 

The GGM needs to be GGM (gm) to match what it is in the slicer:

[AhmedMedhat]

that's great!! but still the greater than or equal has problems
it should be colored green as the target is 99.5% as well

[SamWiseOwl]

Hmm my guess is somewhere the rounding is being a bit odd.

Make sure they are set to the same.

        "Weigher Efficiency (%)", SWITCH ( TRUE (), FORMAT([Weigher Efficiency (%)],"0.00%") >= FORMAT([Weigher Efficiency T],"0.00%"), "green","red" ),

Here I hardcoded the formatting but that is a bandaid on a bigger issue!

[SamWiseOwl · Accepted solution]

Hi @AhmedMedhat 

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
    )