Advanced › RANKX, ALL47 / 50

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

MediumFreeRANKXALL

RANKX + ALL: branch rank per year

Every year end, the branch with the highest sales gets a team bonus. Reza wants a matrix with each branch's rank in each year, one that updates when the year or menu category slicer changes. The measure [Total Sales] already exists.

Task

Create the measure Peringkat Cabang (branch rank) in the Sales table with RANKX: rank the branch by [Total Sales] among all branches (ALL ( Store[StoreName] )), 1 = highest sales.
Visual: branch (Store[StoreName]) × year (Calendar[Year]).

MeasurePeringkat Cabangin table Sales

Required functionsRANKXALL

Expected result

Loading the expected result…

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

Hint 1
In a visual cell the branch filter holds a single branch. To have something to compare against, the table that RANKX iterates must ignore that branch filter.
Hint 2
ALL ( Store[StoreName] ) returns every branch name regardless of the visual filter, while other filters (such as the year) still apply.
Hint 3
RANKX ( ALL ( ___ ), ___ )

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

Peringkat Cabang

Peringkat Cabang = RANKX ( ALL ( Store[StoreName] ), [Total Sales] )

Explanation

In every cell, RANKX iterates the 6 branches from ALL ( Store[StoreName] ), evaluates [Total Sales] for each (the year filter from the visual column still applies), then finds the position of the branch in that row. As a result Cabang Dago ranks 2 in 2024 but drops to 3 in 2025, swapping places with Cabang Kemang.

Without ALL, the iterated table only contains the branch of the cell itself, so every branch ranks 1.
The Total row shows 1 as well, because the sales of all branches together beat any single branch. Hide it with IF ( ISINSCOPE ( Store[StoreName] ), ... ).
Unlike RANKX in a calculated column (static), this measure is recalculated with the slicers.

Data model: kopi· table Sales

Peringkat Cabang =

Loading the DAX editor…

Write the DAX expression, then press Check answer.

Validation result