Forum Discussion
Conditional formating based on cell another cell in a different table
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.
- 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 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
5 Replies
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
Great MFelix thanks!
May I ask what is ,1,0 in the formula and why you used them?
- 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