Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 , 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/
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |