Showing results for 
Search instead for 
Did you mean: 

Conditional formatting on top 3 values from each row in matrix visual


i need to format top 3 values from each row in matrix format . i tried to use top N function on conditional formatting but it didn't workd for me. is there any way to get conditional formatting on matrix . 

here i am attaching sample output i need. 


sample output filesample output file

Helper IV
Helper IV

Hi, if I understand this correctly you do not have the representation of location and sales quantity in one table?
There must be some id that connects the tables, that is some location ID in the sales table, otherwise you would not be able to produce your above table at all. So, let's assume you have this ID (key).

Furthermore assume that we stick to the calculated column approach und do not use a measure in a visual
Then ther are several ways to achieve this:
a) merge the two tables with the ID in Power Query (this would be equivalents to a LEFT JOIN in SQL)
b) calculate a column in sales table to add the location like 

LocationName = 
var ID = SalesTable[ID] // this is the location ID in the sales table
calculate(firstnnonblank(LocationTable[LocationName],0), filter(LocationTable, LocationTable[ID]
= ID))

// assumming that the Location ID found in the sales table is the exactly the ID in the location table

This is basically a lookup of the location name in the location table to be inserted into the sales table
Best regards 





Helper IV
Helper IV

Hi, lets say you have produced a table with the columns location, time and qty. Then you can either create a measure or a calculated column for this table. Here I take a calculated column and the top 2 values

IsTop2 = 
var loc = TestTable[Location]
var thisQty = TestTable[Qty]
var ct = CALCULATE(count(TestTable[Qty]), FILTER(TestTable, TestTable[Location] = loc && TestTable[Qty] > thisQty))

if(ct < 2, "#009292", blank())

 You then use the conditional formatting menu and select format by field and reference the IsTop2 field. 
This highlights for example the background of the top 2 in some hex color as above. 
I produced a table that will already shows the pivot values of the visual. The surely can be modified, but the principle should be the same.

Best regards 



thank you for the solution , but i am still facing challange Reason is  i have column in multiple small tables  so i am not able to create a column . any idea on this?


location column is in location table 

sales in sales table . 

challange is , while creating a column the location / sales table us  is not detected.

Helpful resources

Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors