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

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors