topic Re: PowerBI Table - How to colour code cells based on values in the row in Developer

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

SKPowerBINewbie — Tue, 05 Jul 2022 10:10:25 GMT

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.

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

Smalfly — Thu, 07 Jul 2022 14:34:13 GMT

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...

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

Anonymous — Fri, 08 Jul 2022 09:38:20 GMT

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.

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

SKPowerBINewbie — Mon, 11 Jul 2022 11:09:54 GMT

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

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

Anonymous — Tue, 12 Jul 2022 09:15:19 GMT

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.

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

SKPowerBINewbie — Tue, 12 Jul 2022 22:43:39 GMT

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.