cancel
Showing results for 
Search instead for 
Did you mean: 
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 IV
Super User IV

@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/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

5 REPLIES 5
amitchandak
Super User IV
Super User IV

@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/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

@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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak Much Appreciated!

@amitchandak  Omg this is perfect! THANKS so much!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors