Show Top Product per Category Based on Sales

[Anonymous]

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!

[az38 · Accepted 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

LinkedIn