Forum Discussion
Helper IConditional formatting just for some cells in a matrix
I have a matrix with this structure:
NAME Program Actual
| Kpi1 | R1 | 66.5 |
| Kpi1 | R2 | 43.5 |
| Kpi1 | R3 | 70.9 |
| Kpi2 | R1 | 2 |
| Kpi3 | R2 | 2 |
| Kpi4 | R3 | 3 |
Name and Program are columns, Actual is a measure.
How can I use conditional formatting for the Actual field only for the first 3 cells (when NAME = Kpi1)?, so, if ACTUAL > 80% -> green , <75 -> red, between 75-80 no color.
Add a measure to set the formatting color and check for the KPI1 row like this.
Format Color = SWITCH ( TRUE(), SELECTEDVALUE ( YourTable[Name] ) = "KPI1", SWITCH( TRUE, [Actual] <= 75, "Red", [Actual] >= 80, "Green" ) )Then apply that measure as the background color to the value:
9 Replies
- jdbuchanan71
Super User
Add a measure to set the formatting color and check for the KPI1 row like this.
Format Color = SWITCH ( TRUE(), SELECTEDVALUE ( YourTable[Name] ) = "KPI1", SWITCH( TRUE, [Actual] <= 75, "Red", [Actual] >= 80, "Green" ) )Then apply that measure as the background color to the value:
savulescThis is not working for me, all the rows are green even if they should not.
- jdbuchanan71
If your [Actual] measure is a % then you would apply it at .75 and .8 like this.
Format Color = SWITCH ( TRUE(), SELECTEDVALUE ( YourTable[Name] ) = "KPI1", SWITCH( TRUE, [Actual] <= .75, "Red", [Actual] >= .80, "Green" ) )
- parry2k
savulesc you cannot use conditional formatting on a column used in the rows but if you are using table visual, you can apply conditional formatting on the column on a row. Add a new measure for conditional formatting and then use table visual and apply field value for Name column using the color measure
Color KPI = VAR __actual = [Actual Measure] RETURN SWITCH ( TRUE(), __actual > .80, "Green", __actual < .75, "Red" )✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
- savulesc
I didn't really understand your response, but I don't want to use conditional formatting on the Name column , because that doesn't help me at all.
- parry2k
jdbuchanan71 Thanks for correcting. I missed the KPI name condition but you cannot apply conditional format on a column on the row in the matrix visual, visual still need to be changed to table visual
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
- jdbuchanan71
Can you post your .pbix file (load it to drop box and share the link)? My formula works in my test.
- savulesc
I found the issue. For some reason i had to put <= "65%" in the measure. Thanks for your help.
