Filter Problem - Show Top 3 Companies for a particular category

[lwklwk98]

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.

[amitchandak · Accepted solution]

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

[lwklwk98]

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

[amitchandak]

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

[lwklwk98]

@amitchandak Much Appreciated!

[lwklwk98]

@amitchandak  Omg this is perfect! THANKS so much!