Forum Discussion
Conditional Formatting for a group within a table
Hi all!
I currently have a table that looks like this:
| Store | Aisle | Item | Total Purchased |
| Walmart | 13 | Crackers | 16 |
| Walmart | 13 | Fish | 18 |
| Walmart | 5 | Grapes | 15 |
| Whole Foods | 20 | Bread | 2 |
| Whole Foods | 23 | Celery | 10 |
| Whole Foods | 22 | Apples | 8 |
I have them in expandable columns. So, for example, if I click on Walmart, it expands to show aisle number. If I click on aisle number, it expands to show food item. I currently have conditional formatting to shade the background of each cell from red to green based on how much it sells. Red = Least Sold, Green = Most Sold.
When all of it is unexpanded, it shows just the total sold at Walmart and the total sold at Whole Foods, so the Walmart cell is green and the Whole Foods cell is red. That's good, that's how I want it to be. But when I expand to the aisle level under Walmart, the Grapes record is shaded green (because it's higher than all of the values at Whole Foods, so on a holistic level it sells more). However, I want the Grapes record to be shaded red, because it is the lowest sold item at Walmart. I want it to be compared amongst the items within the same group, as opposed to every item in the entire table. Is there a way to accomplish this?
TL;DR: I'm looking to have conditional formatting change to the lowest level of detail whenever a group is expanded in a spreadsheet report in Power BI. Is that possible?
5 Replies
hi Anonymous - You can implement the same by creating a rank for the product performace within each Customer / Product segment and use the rank to apply conditional formatting.
Below is the DAX that was used to create the Rank column -
Rank by District =IF (HASONEVALUE ( 'Location'[District] ),RANKX ( ALLSELECTED ( 'Location'[Branch] ), ( [Labor % Sales] ), ,ASC, Skip ))Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!
hi Anonymous - You do not need to add the rank to the matrix - I have added it in the screenshot for your reference. You can create the rank measure and use it for conditional formatting even without adding it to the matrix.
Also can you please share a small sample of the data and the measure you have created so I can idenitify why it is calculating as blank for you?
Also please mark the response a solution so others can also easily access for a similar problem - also please provide a 👍 if my comment helped with solving your issue. Thanks!
Anonymous , what I think of is having rank ate few levels and use insincope to change them based on level and use that that one the color scale
example
Category Rank = RANKX(all(Table[category]),[Sales])
Sub Category Rank = RANKX(all(Table[Sub category]),[Sales])
overall Rank
SWITCH (
TRUE,
ISINSCOPE ( Table[Category ] ), [Category Rank],
ISINSCOPE ( Table[Sub Category] ), [Sub Category Rank],
blank()
)refer
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values
