cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

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

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

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

Helper I

@amitchandak Much Appreciated!

Helper I

@amitchandak  Omg this is perfect! THANKS so much!

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors