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!

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

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'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.