Conditional Formatting in Matrix Visual

[Anonymous]

Hi Folks,

I have an interesting scenarios in Power BI Matrix visual. First look at the following final output image.

In rows section i have 3 fields TimeZone, Area, and Segmen. In values secton i have some metric.

The ask here is i need to highlight the top 3 and bottom 3 values irrespective of the drill down.

For example, in the above image first TimeZone "Americas" expanded upto Segment level and the other two TimeZones are not expanded upto Segment level. Now the conditional formatting ask is even though below two are not expanded to Segement i still need to consider whatever value is visible in the entire value column and highlight the top 3 and bottom 3.

In the image above bottom 3 values are as follows:
US - EC MFG (Segment level)  = 88%

ANZ - Enterprise (Area Level) = 76%

Western Europe - Enterprise (Area Level) = 88%

Top 3 Values are as follows:
Switzerland - Enterprise (Area Level) = 115%

US - HPSI HLS (Segment Level) = 104%

US - EC RCG (Segment Level) = 104%

is there any way we can do this kind of conditional formatting for the visible level values (one is expanded others are not) in matrix visual in Power BI.

@amitchandak @Ritaf1983 @Greg_Deckler @Anonymous @Idrissshatila @danextian @rajendraongole1

[Anonymous]

Hi,

Thanks for the solution amitchandak  offered, and i want to offer some more infotmation for user to refer to.

hello @Anonymous  after testing, in matrix, it can filter the top/bottom 3 at the current level, it cannot filter the top3/bottom3 across the level.

Best Regards!

Yolo Zhu

[amitchandak]

@Anonymous ,

Create two measure like

top =

Rank(dense, Allselected(Table, Table[Zone], Table[Area], Table[Segment]),Orderby([Your Measure], desc),partitionBy(Table[Zone]) )

Bottom = Rank(dense, Allselected(Table, Table[Zone], Table[Area], Table[Segment]),Orderby([Your Measure], asc),partitionBy(Table[Zone]) )

Now using thise you can create another meausre when you have create coloe and use them in conditional formatting using field value option

Color = Switch(true() ,

[Top] =1 || [Bottom]=1, "Green",

// Other coditions

)

You can also use rownumber if needed

Power BI - New DAX Function: RANK - How It Differs from RANKX: https://youtu.be/TjGkF44VtDo

Rownumber- https://www.youtube.com/watch?v=yS9-IQjUDwg&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L&index=1

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
https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pane-66e0afcb15f3