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 Community,
I'm having trouble with a DAX formula showing the top product in a category. I have a data model with a sales table and a product table. In the report view I would like to make a table containing the category name, sum of total sales for this category, the top selling product within the category. I only need one product per category. The resulting table should look like this:
Product Category | Sum of Sales | Top Product Based on Sales |
Fruit | 10000 | Banana |
vegetables | 9000 | Carrot |
Bread | 8000 | White Casino |
I've looked around but couldnt find a article explaining the formula I need. If anybody has any suggestions or knows a related article that would be very helpfull.
Thanks in advance!
Solved! Go to Solution.
Hi @Anonymous
what rule should be applied for a tie-situation when two or more products have the same top sales within category?
anyway, you could add a calculated column
Rank = RANKX(FILTER(ALL('Table');'Table'[Product Category]=EARLIER('Table'[Product Category]));'Table'[Sales])
then create a measure
Top Product Based on Sales = calculate(FIRSTNONBLANK('Table'[Product];1);ALLEXCEPT('Table';'Table'[Product Category]);'Table'[Rank]=1)
add this measure to visual and set aggregation as Sum for field [Sales] in visual fields settings
do not hesitate to give a kudo to useful posts and mark solutions as solution
Hi @Anonymous
what rule should be applied for a tie-situation when two or more products have the same top sales within category?
anyway, you could add a calculated column
Rank = RANKX(FILTER(ALL('Table');'Table'[Product Category]=EARLIER('Table'[Product Category]));'Table'[Sales])
then create a measure
Top Product Based on Sales = calculate(FIRSTNONBLANK('Table'[Product];1);ALLEXCEPT('Table';'Table'[Product Category]);'Table'[Rank]=1)
add this measure to visual and set aggregation as Sum for field [Sales] in visual fields settings
do not hesitate to give a kudo to useful posts and mark solutions as solution
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 |
---|---|
49 | |
27 | |
20 | |
15 | |
12 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |