Conditional Formatting not working

[EpicTriffid]

I'm trying to set conditional formatting in a table. Because of the different data types, two fields (Tar Diff and Int Tar Diff) have been combined in a measure with the correct formatting so that they can go in one graph.

I am then applying an if statement to give me background colors based on if they are above zero or not. Because of management suggestions, its actually closer whether it is below 0.5 or not.

You can see in the picture below the table in question and the offending code.

Tar Diff is a %, hence the extra decimals, and Int Tar Diff is a whole number. 

No matter how I change the formula, that -1 still stays green! Completely baffled!

[v-lid-msft]

Hi @EpicTriffid ,

How do you combine two fields into a measure? Maybe in this context, you show [Int Tar Diff] as -1, but it calculate [Tar Diff] first in color measure and get the green color.

Could you please describle the formula about combing [Int Tar Diff] and [Int Tar Diff]? Please don't have any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EpicTriffid]

Hi @v-lid-msft 

So, it my data I have values either in percentages, or in whole numbers. Some of these are actuals, and some targets. We show the actuals as they are, but we use targets in a Difference from Target measure, which just subtracts Actuals from Targets. We have to do this in one measure for the whole numbers (Int Tar Diff), and in another measure for percentages (Tar Diff). An example of this code is below:

Tar Diff = 
IF(SUM(KPIs_COMBINED_RESHAPED[KPI Targets]) <> 0 && (SUM(KPIs_COMBINED_RESHAPED[KPI Actuals])) <> 0,
SUM(KPIs_COMBINED_RESHAPED[KPI Actuals]) - SUM(KPIs_COMBINED_RESHAPED[KPI Targets]),"")

We then need to combine both of these measures into one table, but so we can whole numbers and percetnages side by side. So to format that I use:

Tar Diff Table = SWITCH(VALUES(KPIs_COMBINED_RESHAPED[KPI]), "INT_ENR", FORMAT([Int Tar Diff], "0"), FORMAT([Tar Diff],"0%"))

This then allows the data to show up as it does above. 

[v-lid-msft]

Hi @EpicTriffid ,

We can try to use the following measure as the new field value to meet your requirement:

ColorMeasure =
IF (
    [Int Tar Diff] <> ""
        || [Tar Diff] <> "",
    SWITCH (
        VALUES ( KPIs_COMBINED_RESHAPED[KPI] ),
        IF ( [Int Tar Diff] >= 0.5, "#30a316", "d10a14" ), IF ( [Tar Diff] >= -0.005, "#30a316", "d10a14" )
    ),
    ""
)

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EpicTriffid · Accepted solution]

Hi @v-lid-msft,

Unfortunately that just made everything blank in terms of colour!

I did, however, find a solution:

TarKPIColour = 
IF ([Int Tar Diff] <> "" || [Tar Diff] <> "",
IF ([Tar Diff] >= -0.005 && [Int Tar Diff] >= 0.5, "#30a316", "#d10a14"))

Not entirely sure why, but it works!

[v-lid-msft]

Hi @EpicTriffid ,

Glad to hear that you have resolved your problem. Would you please kindly mark your sharing solution as an answer so that it can benefit more users?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @EpicTriffid 

The only way I could make it works was doing the following metric:

TarKPIColour = SWITCH (

   SUM([Tar Diff]) >= -0.005, "#30a316",

   SUM([Tar Diff])  <-0.005, "#d10a14",

...)

[EpicTriffid]

hi @Anonymous 

Thanks for that. I had to use SUMX in order to get the measure, but the visualisation failed saying it can compare boolean values with text values. Not sure where it's getting hung up...