Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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

Hello, 

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

3 REPLIES 3
scee07
Resolver I
Resolver I

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
return 
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 

 

Christian

 

 

scee07
Resolver I
Resolver I

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))

return 
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 

 

Christian

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?

ex:

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

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors