Forum Discussion
Helper IVConditionally formatting % values in a table based on slicer selection
I have a requirement where the background color of the numbers needs to be changed based on the slicer selection. The sales column is of the "Percentage" data type.
- If the Region is selected as East, the sales is less than 50% then "RED" else "GREEN"
- If the Region is selected as West, the sales are less than 60% then "RED" else "GREEN"
| Country | Population | Sales |
| Sweden | 119,997 | 50% |
| Germany | 16,777 | 70% |
| USA | 10,000 | 30% |
| India | 34,078 | 40% |
| Netherlands | 46,767 | 90% |
| Japan | 78,999 | 65% |
Slicer values:
Region:
- East
- West
Marico , a Color measure
Switch( true() ,
selectedvalue(Table[Region]) = "East" && [Sales] <.5, "Red" ,
selectedvalue(Table[Region]) = "East" && [Sales] >=.5, "Green" ,
selectedvalue(Table[Region]) = "West" && [Sales] <.6, "Red" ,
selectedvalue(Table[Region]) = "West" && [Sales] >=.6, "Green" )
Use this measure in conditional formatting using field value option
How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/1682539
4 Replies
- amitchandak
Super User
Marico , a Color measure
Switch( true() ,
selectedvalue(Table[Region]) = "East" && [Sales] <.5, "Red" ,
selectedvalue(Table[Region]) = "East" && [Sales] >=.5, "Green" ,
selectedvalue(Table[Region]) = "West" && [Sales] <.6, "Red" ,
selectedvalue(Table[Region]) = "West" && [Sales] >=.6, "Green" )
Use this measure in conditional formatting using field value option
How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/1682539 
Maricoamitchandak Kudos! This works well.
However, I have 5 such fields like Sales where the above type of conditional formatting needs to be applied in the table.
1. Would I need to create 5 different measures for each field like the one above?
2. Will combining in the single measure and using it in conditional formatting (field value) would also work?
3. What will happen when user selects "Select All" option instead of either "East" or "West"?
Appreciate your support!
- amitchandak
Marico , Assume another measure is margin and you want to color margin based on margin %, then I would advise creating a new measure.
if every thing is based on sales , then just use switch , if etc and create one measure
- Marico
amitchandak Sure, thanks
What will happen when user selects "Select All" option instead of either "East" or "West" - Will it show the values based on threshold of East or West in that case?
Client has a requirement wherein they want to have the "Select All" option enabled in the slicer along with "East" and "West".