Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi ,
I have a Power BI report that dispalys data in a table that displays data like this
Criteria | M1 | M2 | M3 | M4 | M5 | M6 | M7 | M8 | M9 | M10 |
Cr1 | 1 | 55 | 32 | 45 | 11 | 2 | 3 | 33 | 4 | 61 |
Cr2 | 55 | 4 | 5 | 44 | 21 | 33 | 2 | 5 | 55 | 22 |
Cr3 | 0 | 77 | 5 | 66 | 44 | 2 | 21 | 59 | 95 | 3 |
Cr4 | 5 | 52 | 8 | 60 | 5 | 55 | 76 | 74 | 12 | 55 |
Cr5 | 22 | 33 | 44 | 55 | 32 | 22 | 32 | 63 | 59 | 54 |
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.
Criteria | Colour Cell Red when Criteria > than |
Cr1 | 10 |
Cr2 | 21 |
Cr3 | 5 |
Cr4 | 22 |
Cr5 | 40 |
The Final table should look like this
How do I achieve this in PowerBI. Any guidance will be much appreciated.
Solved! Go to Solution.
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.
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 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 ,
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.
Hi @SKPowerBINewbie ,
try to follow these steps: