Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Conditional Formatting not behaving as expected

Hi All,

 

I have set up conditional formatting on a Matrix table with 4 levels of rows, values has 2, revenue and the % of that revenue against the Row total... 

 

Now I put the conditional formatting on this % field and set it 0-5% = Red 6-10% = Amber 11-100% = Green.

 

But whatever logic PowerBi is using its not making sense on the table, ie I have a row with 12% in the % field but its coming up as Red? 

 

What does PowerBi work its Percentages off and is there a way to change this? or some alternative.

 

Thanks,

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi Anonymous ,

    I create a simple sample that you could have a try. And you can use any of the values listed in the CSS color spec at https://www.w3.org/TR/css-color-3/ to color your visuals. In my sample, I use text to color it.  Please view the sample below.

    Conditional formatting = 
SWITCH (
    TRUE (),
    'Table'[%] <= 0.05, "Red",
    'Table'[%] <= 0.1, "Yellow",
    "Green"
)

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    6 years ago

    Hi @rgodavarthy ,

    It is by design. The percentage is calculated based on Range.

    Percentage(in conditional formatting)

    You could try to create DAX formulas to apply that color to the background of font color of a table or matrix visual. For more details, you could try my sample.

    Reference:

    https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-formatting-by-field-value

     

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you v-xuding-msft this is really helpful!

       

      I am struggling as to how to implement it though as I am not measuring against a goal but a % of the Row total in a Matrix so the only field I have is Revenue against millions of rows.

       

      The Matrix looks something like this

       

      Line of BusinessWindows Doors Flooring Total 
      CountryRev%Rev%Rev%Rev%
      UK25.151%11.423%1326%49.5100%
      France10.69%100.282%119%121.8100%
      Germany30.144%18.928%19.528%68.5100%
      Total65.827%130.554%43.518%239.8100%

       

      There are sublevels below those in the matrix also going down at least 4 or more levels.

       

      Thanks again for the help!

      • v-xuding-msft's avatar
        v-xuding-msft
        Community Support
        6 years ago

        Hi Anonymous ,

        I create a simple sample that you could have a try. And you can use any of the values listed in the CSS color spec at https://www.w3.org/TR/css-color-3/ to color your visuals. In my sample, I use text to color it.  Please view the sample below.

        Conditional formatting = 
SWITCH (
    TRUE (),
    'Table'[%] <= 0.05, "Red",
    'Table'[%] <= 0.1, "Yellow",
    "Green"
)

        Best Regards,

        Xue Ding

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.