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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
lwklwk98
Helper I
Helper I

Filter Problem - Show Top 3 Companies for a particular category

Hello!

 

I really need help with this particular question.

 

Here is a mock dataset:

Product Type   Companies    Profit ($)    
AHop1000
ATop200
ACop0
BHop200
BTop0
BCop3000
CHop0
CTop0
CCop10000
DHop100
DTop0
DCop300
EHop300
ETop0
ECop0
FHop2000
FTop100
FCop1000

 

You can assume that there are A to F different types of products. There are a lot more companies and as you can infer that not all companies bring in sales for every product. 

 

I want to create a table/matrix visualization that shows the Top 3 Companies for each Product and its respective profit amount. I can not seem to filter this properly and I would appreciate any help!.

 

Thank you very much.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@lwklwk98 , Create a measures like

 

Profit M = sum(Table[Profit ($)])

rank = rankx(filter(allselected(Table[Product Type], Table[Companies]), [Product Type] = max([Product Type])), [Profit M])

 

and filter for Rank <=3

 

 

also refer

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@lwklwk98 , Create a measures like

 

Profit M = sum(Table[Profit ($)])

rank = rankx(filter(allselected(Table[Product Type], Table[Companies]), [Product Type] = max([Product Type])), [Profit M])

 

and filter for Rank <=3

 

 

also refer

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

@amitchandak 

 

Sorry to bother you on this again, but can I ask if there is a way to do a double filter? 

 

I have the desired table but when I sort by each Product Type, the ranking will be jumbled up like 1,3,2 etc. I hope that the rank can be 1,2,3 consistently for all the product types.

 

I searched online and there isn't any double sorting so I was wondering if there is any quick method to resolve this? 

 

Thank you so much!

@lwklwk98 , In the table visual you can sort on two column. Sort on one column, with Shift pressed click on another column.

@amitchandak Much Appreciated!

@amitchandak  Omg this is perfect! THANKS so much!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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