Tag Archives: RANKX

Top products within each category in DAX

Posted on by • Posted in Advanced • Tagged , , , , • Leave a comment

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

How to use “RANKX”

Posted on by • Posted in Basic script • Tagged , , , • Leave a comment

If you want to rank your result in DAX you can use the RANKX function.

Below is a sample

DEFINE
MEASURE 'Internet Sales'[Rank] =
RANKX (
ALL ( 'Product'[Product Name] ),
SUMX (
RELATEDTABLE ( 'Internet Sales' ),
'Internet Sales'[Internet Total Sales]
)
)
EVALUATE
FILTER (
ADDCOLUMNS (
VALUES ( 'Product'[Product Name] ),
"Product Rank", 'Internet Sales'[Rank],
"Sales amount", 'Internet Sales'[Internet Total Sales]
),
[Sales amount] <> 0
)
ORDER BY 'Internet Sales'[Rank]

Please note the filter that removes the empty rows.

Here is the resultset

Rank