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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s