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.
Hi,
I'm totally new with Power BI and already ran in trouble when trying to create this simple table.
I need to create a table which has top 20 products sold for each product category. For example, if I have three product categories which have 100 products each, I need to create a table which has 60 products in total grouped in 3 categories (3 categories x top 20 products).
Could someone give some pointers how to solve this?
Br, Pekka
Solved! Go to Solution.
Hi @plaa,
If I understand you correctly, you should be able to follow steps below to create the table which has top 20 products sold for each product category.
1. Use the formula below to create a measure to calculate Total Sales.
Total Sales = SUM(Sales[Revenue])
2. Use the formula below to create a calculate column in Product table to rank product sold for each product category.
Rank = RANKX ( ALLEXCEPT ( Products, Products[Category] ), [Total Sales], , DESC, DENSE )
3. Then you should be able to use the formula below to create the table.
Table = SELECTCOLUMNS ( FILTER ( Products, Products[Rank] <= 20 ), "Category", Products[Category], "Product", Products[Product], "Rank", Products[Rank] )
Regards
Hi @plaa,
If I understand you correctly, you should be able to follow steps below to create the table which has top 20 products sold for each product category.
1. Use the formula below to create a measure to calculate Total Sales.
Total Sales = SUM(Sales[Revenue])
2. Use the formula below to create a calculate column in Product table to rank product sold for each product category.
Rank = RANKX ( ALLEXCEPT ( Products, Products[Category] ), [Total Sales], , DESC, DENSE )
3. Then you should be able to use the formula below to create the table.
Table = SELECTCOLUMNS ( FILTER ( Products, Products[Rank] <= 20 ), "Category", Products[Category], "Product", Products[Product], "Rank", Products[Rank] )
Regards
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 |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |