Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Conditional formatting based on total column

Hi!

 

I have a matrix which looks like this:

The total column shows the average for the respective rows. I want to apply conditional formatting to the values based on the value in the Total Column. If the value is lower than the value in the total column it shoult be displayed in green and if it is higher it should be red. How can I achieve this?

 

Thank you! 

Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi Anonymous ,

     

    You need to create a measure for the condittional formatting something similar to:

     

    Coditional formatting = IF([Average]>= CALCULATE([Average];ALLSELECTED('Table'[Columns Category]));"#517d4f";"#b81818")

[Average] - Measure used for filling out the values in the matrix

If there is none create the following:
Average = Average(Table[Values])

Table[Columns Category] - the name of the column used on your columns in the matrix

     

    Then use the condittional formatting Field Value and chosse the previous measure.

     

    You can adjust the values in "" to the colours you want.

3 Replies

  • Wimverh's avatar
    Wimverh
    Resolver IV
    6 years ago

    you have to create a new measure, which you can use for formatting. This measure should calculate the total value, on each field on your matrix. You can achive that by the ALL function. Your screenshot does not show the measure on which the columns are based. 

    let's assume that column is mydimesion[mytype], your used measure is [mymeasure]

     

    your formatting measure should be

    myformatting measure=
calculate([mymeasure],ALL(mydimension[mytype]))
  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    Hi Anonymous ,

     

    You need to create a measure for the condittional formatting something similar to:

     

    Coditional formatting = IF([Average]>= CALCULATE([Average];ALLSELECTED('Table'[Columns Category]));"#517d4f";"#b81818")

[Average] - Measure used for filling out the values in the matrix

If there is none create the following:
Average = Average(Table[Values])

Table[Columns Category] - the name of the column used on your columns in the matrix

     

    Then use the condittional formatting Field Value and chosse the previous measure.

     

    You can adjust the values in "" to the colours you want.

    • omelo's avatar
      omelo
      Helper III
      3 years ago

      Hi, I need some help, I'm failing at accomplishing this simple thing in Power BI.

      I have a matrix with a few columns, one of them is a measure called DSO.
      I would like to color DSO based on its total. If >=total,"Red"; if < Total, "green"

       

      Thank you