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.

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.

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors