Forum Discussion
Conditional formating based on cell another cell in a different table
- 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
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