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.
I have two CSV files that look like this:
Data
Unit,CategoryA,CategoryB,CategoryC
U1,99,90,82
U2,92,100,56
U3,96,50,66
U4,85,33,99
U5,21,94,100
Cutoffs
Unit,Cutoff
U1,95
U2,90
U3,55
U4,85
U5,90
I load them to a table and combine and display them like this, using the Unit column to combine the two:
Now I would like to conditionally format columns Category A through C, based on the Cutoff column. For example, for Unit1, if any category value >= 95, set that cell background to green, and if category value < 95, set it to red. So, my formatted data should look like this:
The available Conditional Formatting options don't seem to help me do this. Is that the case, or is there a work around that will alow me do this?
Solved! Go to Solution.
Hi @Anonymous
If you cannot do this, you need to use visual Table and then write 3 measures (separate for each category) that will return the color code.
CC Category A =
IF(SELECTEDVALUE('Table (3)'[Category A]) >= SELECTEDVALUE('Table (3)'[CutOff]), "#0f0","#f00")
CC Category B =
IF(SELECTEDVALUE('Table (3)'[Category B]) >= SELECTEDVALUE('Table (3)'[CutOff]), "#0f0","#f00")
CC Category C =
IF(SELECTEDVALUE('Table (3)'[Category C]) >= SELECTEDVALUE('Table (3)'[CutOff]), "#0f0","#f00")
Then for each column separately (Cat A, B, C) set the background color from conditional formatting section. Select the Field Value from the list, and then select the appropriate measure.
_______________
If I helped, please accept the solution and give kudos! 😀
Hi @Anonymous
You should follow these steps.
1. Unpivot Category - put categories in one column and values in the other.
2. Create a measure that checks whether a given category value is greater than or less than CutOff. This measure will return a color code (red or green).
ConditionalColoring =
IF(SELECTEDVALUE('Table'[Value]) >= SELECTEDVALUE('Table'[CutOff]), "#0f0","#f00")
3. In the conditional formatting settings, select Field Value and then select the measure you created.
4. The Result:
I prepared the file with the solution. Please use this link to find it: https://gofile.io/d/Z3szIL
_______________
If I helped, please accept the solution and give kudos! 😀
@lkalawski thanks for the quick reply.
While this does seem to get the end result I want, it changes my data structure, which is something I don't want to do. I prefer to not unpivot category as I use it for other things. So I'm guessing there's no way to do this without changing how my data is organized?
Hi @Anonymous
If you cannot do this, you need to use visual Table and then write 3 measures (separate for each category) that will return the color code.
CC Category A =
IF(SELECTEDVALUE('Table (3)'[Category A]) >= SELECTEDVALUE('Table (3)'[CutOff]), "#0f0","#f00")
CC Category B =
IF(SELECTEDVALUE('Table (3)'[Category B]) >= SELECTEDVALUE('Table (3)'[CutOff]), "#0f0","#f00")
CC Category C =
IF(SELECTEDVALUE('Table (3)'[Category C]) >= SELECTEDVALUE('Table (3)'[CutOff]), "#0f0","#f00")
Then for each column separately (Cat A, B, C) set the background color from conditional formatting section. Select the Field Value from the list, and then select the appropriate measure.
_______________
If I helped, please accept the solution and give kudos! 😀
@lkalawski OK, that worked, thanks!
Although that's a ridiculous amount of work for something supposedly simple. Microsoft really need to add simple features like this.
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 |
---|---|
107 | |
94 | |
77 | |
63 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |