Conditional formating based on cell another cell in a different table

[Anonymous]

Hi,

I have 2 tables as shown below. The left table is Actual and the right one is Target. I need to compare each value in the left table with its counterpart in the right table and if it's smaller number, then turn it into red otherwise no change!

What conditional formatting rule I have to apply?

The left table is summarized of 234 rows and 4 cloumns and the right one is obtained as is from Excel table called target.

[MFelix · Accepted solution]

Hi @Anonymous,

The conditional formatting only works with numbers, so the easist way to make it work is to considerer the result a 0 or a 1, easy to remember when settibg up condittional formatting.

You could also make it trough a difference and on conditional for matting everything lower than 0 would be marked.

Regards,
MFelix

[MFelix]

Hi @Anonymous ,

You need to have the two tables in a similar format basically:

Actuals:

Type         Cat         Value

MEW 0	BORID	1
MEW 0	CHIRT	24
MEW 0	SANG	30
MEW 0	XY	20
MEW 0	EZ	10
MEW F0	STR	4
MEW F0	BORID	1
MEW F0	CHIRT	2
MEW F0	SROOK	1
MEW F0	XY	1
MEW F0	EX	1

Target:

TypeCatValue

MEW 0	BORID	1
MEW 0	CHIRT	28
MEW 0	SANG	20
MEW 0	XY	14
MEW 0	EZ	10
MEW F0	STR	2
MEW F0	BORID	1
MEW F0	CHIRT	1
MEW F0	SROOK	1
MEW F0	XY	1
MEW F0	EX	1

If the target table is on the format you present on the image you need to unpivot columns.

Then create two tables with unique values for Type and Category and make a relationship between those two tables and the other two.

Create the following measures:

Actuals total = SUM(Actuals[Value])

Target Total = SUM(Targets[Value])

Condittional formatting = IF(Actuals[Actuals total]<Targets[Target Total];1;0)

Now make your matrix based on the following setup:

Rows: Type (from the table with unique values)

Column: Categorry (from the table with unique values)

Values: Actuals totals or Target totals

Add a condittional formatting on the actuals with the following setup:

Check result below and attach PBIX file.

Regards,

MFelix

[Anonymous]

Great @MFelix  thanks!

May I ask what is ,1,0 in the formula and why you used them?

[MFelix · Accepted solution]

Hi @Anonymous,

The conditional formatting only works with numbers, so the easist way to make it work is to considerer the result a 0 or a 1, easy to remember when settibg up condittional formatting.

You could also make it trough a difference and on conditional for matting everything lower than 0 would be marked.

Regards,
MFelix

[Anonymous]

Thanks very much @MFelix .

I had  a small problem in using your suggested solution because all the vaules I have are alphanumeric and I always need to count them instead of summing up. I defined a custom column but is there a better work around you could suggest @MFelix  please?

[TeigeGao · Accepted solution]

Hi @Anonymous ,

Actually, if you want to count them rather than sum them, you can edit the measure to:

Actuals total = COUNT(Actuals[Value])

Target Total = COUNT(Targets[Value])

Condittional formatting = IF(Actuals[Actuals total]<Targets[Target Total];1;0)

Best Regards,

Teige