Iterators (SUMX and more) › RANKX17 / 50

Progress is saved on this device. Sign in or sign up free to keep it in your account.

MediumFreeRANKX

RANKX: best-selling menu rank

Ayu is rearranging the menu board: the best-selling items go on top. She wants the sales rank stored as a column in the Product table so it can be used for sorting.

Task

Create the calculated column SalesRank in the Product table with RANKX: rank every product in the Product table by the [Total Sales] measure (1 = highest sales).

Calculated columnProduct[SalesRank]

Required functionsRANKX

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Run previewCtrlEnter
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
RANKX ( table, expression ) evaluates the expression for every row of the table, then finds the position of the current row's value. The default order is descending (largest = 1).
Hint 2
Use the measure, not SUM ( Sales[Amount] ) directly: a measure triggers context transition automatically, so sales are computed per product.
Hint 3
RANKX ( Product, ___ )

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

Product[SalesRank]

Product[SalesRank] = RANKX ( Product, [Total Sales] )

Explanation

In a Product calculated column, RANKX iterates all 17 products. [Total Sales] is evaluated for each product (context transition turns the product row into a filter), then the current row's value is compared with all of them. Kopi Susu Gula Aren ranks 1.
The classic mistake: RANKX ( Product, SUM ( Sales[Amount] ) ) without CALCULATE triggers no context transition, so every product gets the same total and they all rank 1. Also remember that calculated columns are computed at refresh, so this rank is static and ignores slicers. For a rank that follows slicers, use RANKX inside a measure.

Data model: kopi· table Product

Product[SalesRank] =

Loading the DAX editor…

Write the DAX expression, then press Check answer.

Validation result