Forum Discussion
PowerBI Table - How to colour code cells based on values in the row
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
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
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Rico Zhou ,
Yes, that worked . I ended up created a measure for each column and using conditional formatting . Thank you very much for your help and guidance.
