Top products within each category in DAX

If you want to have top products within each category you can solve this using a quite simple query.

Below is a query that lists each model. And for each model the top 2 products (if there are 2 or more products in that model).

DEFINE
MEASURE 'Product'[Sales] =
SUM ( 'Internet Sales'[Sales Amount] )
MEASURE 'Product'[Ranks] =
RANKX (
ALL ( 'Product'[Product Name] ),
SUMX ( RELATEDTABLE ( 'Internet Sales' ), [Sales Amount] )
)
EVALUATE
FILTER (
ADDCOLUMNS (
GENERATE (
VALUES ( 'Product'[Model Name] ),
TOPN (
2,
VALUES ( 'Product'[Product Name] ),
'Product'[Sales],
0
)
),
"Ranks", 'Product'[Ranks],
"SalesAmount", 'Product'[Sales]
),
[SalesAmount] <> 0
)
ORDER BY 'Product'[Model Name] ASC, 'Product'[Sales] DESC

Please note the filter that removes empty rows. If you remove the filter the query will return weird results.

Here is what the result will look like

Rank advanced

Advertisements