Forum Discussion

SKPowerBINewbie's avatar
SKPowerBINewbie
New Member
4 years ago
Solved

PowerBI Table - How to colour code cells based on values in the row

Hi , 

I have a Power BI report that dispalys data in a table that displays data like this 

CriteriaM1M2M3M4M5M6M7M8M9M10
Cr11553245112333461
Cr25545442133255522
Cr30775664422159953
Cr455286055576741255
Cr522334455322232635954

 

What I want is to colour columns M1 to M10 with colour 'Red' , based on what cell value being > than specific values are indicated below.

So , if Criteria = Cr1 and if any of M1 to M10 is > 10 that cell be colured RED , else colour them Green.

Similarly , if if Criteria = Cr2 and if any of M1 to M10 is > 21 that cell be colured RED , else colour them Green.

 

CriteriaColour Cell Red when Criteria > than 
Cr110
Cr221
Cr35
Cr422
Cr540

 

The Final table should look like this 

How do I achieve this in PowerBI. Any guidance will be much appreciated.

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi SKPowerBINewbie ,

     

    If you want to use conditonal formatting in table visual, there should be multiple columns M1/M2...M10 in table value field, you need to create color measures for each columns.

    Here I create a sample to have a test.

    You should need to create 10 color measures in total for each M column. Here I create a color measure for M1 as a sample.

     

    Color for M1 = 
IF(SUM('Table (table visual)'[M1])> SUM('Table 2'[Colour Cell Red when Criteria > than ]),"Red","Green")

     

    We can drop down and select the columns we need to use conditional formatting in Format.

    Here we select M1 and use [Color for M1] in Field value in Background color.

    You can repeat the above operation to add conditional formatting for other M columns.

     

    Best Regards,
    Rico Zhou

     

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

     

5 Replies

  • Smalfly's avatar
    Smalfly
    Responsive Resident
    4 years ago

    Hi SKPowerBINewbie ,

     

    try to follow these steps:

    • select the table you want to edit
    • go to 'format visual' > cell elements > turn background color on

     

    • This will open a pop-up you can use to set the color rules.

     

    • Repeat this for columns M2 -- M10...
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi SKPowerBINewbie ,

     

    The calculation in Power BI is based on columns. Your data model have multiple columns like M1/M2... and so on. This will complicate your calculations. So I suggest you to transform your data model with Unpivot function in Power Query Editor and then create a measure and use this measure in matrix conditional formatting.

    Table1 will look like as below after unpivoting.

    Relationship:

    Color measure:

    Color =
VAR _Compare_Value =
    CALCULATE (
        SUM ( 'Table 2'[Colour Cell Red when Criteria > than ] ),
        FILTER ( 'Table 2', 'Table 2'[Criteria] = MAX ( 'Table 1'[Criteria] ) )
    )
RETURN
    IF ( SUM ( 'Table 1'[Value] ) > _Compare_Value, "Red", "Green" )

    Conditional formatting and result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

     

    • SKPowerBINewbie's avatar
      SKPowerBINewbie
      New Member
      4 years ago

      Hi Rico Zhou , 

      Thank you very much for taking the time to help me out . 

      Your proposed solution works but I have to use output the report into a matrix visual.

      It will not fit in with my current design of a table matrix 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi SKPowerBINewbie ,

         

        If you want to use conditonal formatting in table visual, there should be multiple columns M1/M2...M10 in table value field, you need to create color measures for each columns.

        Here I create a sample to have a test.

        You should need to create 10 color measures in total for each M column. Here I create a color measure for M1 as a sample.

         

        Color for M1 = 
IF(SUM('Table (table visual)'[M1])> SUM('Table 2'[Colour Cell Red when Criteria > than ]),"Red","Green")

         

        We can drop down and select the columns we need to use conditional formatting in Format.

        Here we select M1 and use [Color for M1] in Field value in Background color.

        You can repeat the above operation to add conditional formatting for other M columns.

         

        Best Regards,
        Rico Zhou

         

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