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



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



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 Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!