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.
Hello,
I have a matrix with facilities listed in the rows and tests listed in the columns. The values in the middle of the table are a measure calculated as the number of times a facility exceeds the test divided by the total number of facilities taking the test.
Test A, B, C
Facility 1 - 33%, 0%, 77.27%
Facility 2 - 66.67%, 77.78%, 100%
Facility 3 - 56.67%, 0%, 33.33%
Test A has a benchmark of 50%, Test B has a benchmark of 70%, Test C has a benchmark of 30%.
I would like to apply conditional formatting to the cell values based on the bench of each column. I believe this can be achieved by adding conditional formatting by field value the formatting doesn't seem to apply in all situations.
Color = SWITCH(TRUE(),'APP'[Test]=A&&[AnswerStatus%]>.5, "#008000", 'APP'[Test]=B&&[AnswerStatus%]>.70, "#008000", 'APP'[Test]=C&&[AnswerStatus%]>.30, "#008000")
My ideal soltuion is:
Test A, B, C
Facility 1 - 33%, 0%, 77.27%
Facility 2 - 66.67%, 77.78%, 100%
Facility 3 - 56.67%, 0%, 33.33%
A bonus would be to even add an additional column that would be a count of the number of greens in a row.
Test A, B, C, COUNT_GREEN
Facility 1 - 33%, 0%, 77.27%, 1
Facility 2 - 66.67%, 77.78%, 100%, 3
Facility 3 - 56.67%, 0%, 33.33%, 2
Thanks so much in advance for the DAX genius 🙂
Ashley
Solved! Go to Solution.
Hi @Anonymous
You need to go to FORMAT section (1).
There choose CONDITIONAL FORMATING (2).
Choose the field to format. Another window will pop up.
Choose RULES (3), and BASED ON FIELD - Will be the new calculated field (4).
In the example, I format NAME according to COUNT of IDs.
Hi @Anonymous
Are you missing a 'else' condition?
i.e.
Color = SWITCH(TRUE(),'APP'[Test]=A&&[AnswerStatus%]>.5, "#008000", 'APP'[Test]=B&&[AnswerStatus%]>.70, "#008000", 'APP'[Test]=C&&[AnswerStatus%]>.30, "#008000", "#ff0000")
Another way is, to create 3 calculated columns for each test result.
i.e.
Column_A_Result = IF(AnswerStatus>0.5,1,2)
Then format the column according to this number/column (1=green,2-red). And you can sum up the 1s (column_sum = SUM(Column_A_Result,Column_B_Result ,Column_C_Result ))
Good luck!
A
Thanks so much for the assist @Anonymous!
The else statement isn't needed with the switch. Instead, I added a >= and a < clause - but no dice.
Exploring the column_result option. If I want to format the column according to this number/column (1=green,2-red), where in PowerBI can I apply the format to each column individually?
Hi @Anonymous
You need to go to FORMAT section (1).
There choose CONDITIONAL FORMATING (2).
Choose the field to format. Another window will pop up.
Choose RULES (3), and BASED ON FIELD - Will be the new calculated field (4).
In the example, I format NAME according to COUNT of IDs.
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |