Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsGreat @MFelix thanks!
May I ask what is ,1,0 in the formula and why you used them?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |