Iterators (SUMX and more) › AVERAGEX14 / 50

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

MediumFreeAVERAGEX

AVERAGEX: average sales per branch

The coffee chain owner wants to know how a "typical" branch performs: the sales of one branch for each menu category per year. The Malioboro branch only opened in June 2024, so not every branch has sales in every category and year.

Task

Create the measure Rata-rata per Cabang (average per branch) in the Sales table with AVERAGEX: iterate the Store table, compute the [Total Sales] measure for each branch, then take the average.
Visual: category (Product[Category]) × year (Calendar[Year]).

MeasureRata-rata per Cabangin table Sales

Required functionsAVERAGEX

Expected result

Loading the expected result…

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

Hint 1
AVERAGEX ( table, expression ) evaluates the expression for each row of the table, then averages the results.
Hint 2
The table to iterate is the list of branches. The expression is the sales measure that already exists.
Hint 3
AVERAGEX ( Store, ___ )

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

Rata-rata per Cabang

Rata-rata per Cabang = AVERAGEX ( Store, [Total Sales] )

Explanation

For every Store row, [Total Sales] is evaluated with context transition: that branch row becomes a filter, on top of the category and year filters from the visual cell. AVERAGEX then averages the results and ignores BLANKs. Example, Non-Kopi 2024: Rp1,073,000 divided by the 5 branches that had sales (not 6) gives Rp214,600.
Common mistakes:

AVERAGE ( Sales[Amount] ) gives the average per transaction, not per branch.
DIVIDE ( [Total Sales], 6 ) is wrong whenever a branch has no sales in that cell.

Context transition only happens because you use a measure. Writing SUM ( Sales[Amount] ) directly inside AVERAGEX would return the same total for every branch.

Data model: kopi· table Sales

Rata-rata per Cabang =

Loading the DAX editor…

Write the DAX expression, then press Check answer.

Validation result