Forum Discussion

EpicTriffid's avatar
EpicTriffid
Helper IV
6 years ago
Solved

Conditional Formatting not working

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 th...
Need Help
  • EpicTriffid's avatar
    EpicTriffid
    6 years ago

    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!

EpicTriffid's avatar
EpicTriffid
Helper IV
6 years ago

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's avatar
v-lid-msft
Community Support
6 years ago

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's avatar
    EpicTriffid
    Helper IV
    6 years ago

    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's avatar
      v-lid-msft
      Community Support
      6 years ago

      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.