cancel
Showing results for
Did you mean:
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 (\$) A Hop 1000 A Top 200 A Cop 0 B Hop 200 B Top 0 B Cop 3000 C Hop 0 C Top 0 C Cop 10000 D Hop 100 D Top 0 D Cop 300 E Hop 300 E Top 0 E Cop 0 F Hop 2000 F Top 100 F Cop 1000

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

Proud to be a Super User!

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

Proud to be a Super User!

Helper I

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!

Super User IV

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

Proud to be a Super User!

Helper I

@amitchandak Much Appreciated!

Helper I

@amitchandak  Omg this is perfect! THANKS so much!

Announcements

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.

Microsoft named a Leader in The Forrester Wave

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

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