Advanced › VAR, RETURN, SUMX44 / 50

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

MediumFreeVARRETURNSUMX

VAR and RETURN: gross margin

Mr. Joko, the owner, asks: out of every Rp100 of sales, how much is left after the cost of goods (COGS)? Intan wants the formula to be easy for other analysts to read, so she uses variables. The measure [Total Sales] already exists; the unit cost is in Product[Cost].

Task

Create the measure Margin Kotor (gross margin) in the Sales table with VAR and RETURN:

one variable for revenue = [Total Sales]
one variable for COGS = SUMX over Sales of Sales[Quantity] × RELATED ( Product[Cost] )
result = (revenue - COGS) / revenue, using DIVIDE

Visual: category (Product[Category]) × year (Calendar[Year]).

MeasureMargin Kotorin table Sales

Required functionsVARRETURNSUMX

Expected result

Loading the expected result…

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

Hint 1
Each VAR stores the result of one expression under a name. After all the VARs, write RETURN followed by the final expression that uses those names.
Hint 2
COGS is computed per transaction row (quantity × unit cost), so it needs the SUMX iterator. RELATED fetches Cost from the Product table in that row context.
Hint 3
VAR Revenue = [Total Sales] VAR Cogs = SUMX ( Sales, ___ ) RETURN DIVIDE ( ___, Revenue )

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

Margin Kotor

Margin Kotor = VAR Revenue = [Total Sales]
VAR Cogs = SUMX ( Sales, Sales[Quantity] * RELATED ( Product[Cost] ) )
RETURN DIVIDE ( Revenue - Cogs, Revenue )

Explanation

Each variable is evaluated once in the cell context and then reused in RETURN. The result: the Kopi (coffee) category has a gross margin of about 67%, Makanan (food) about 59%, and the overall total is 65.1%. Food has the lowest margin because the cost of the main dishes is relatively high.

Without VAR you would write [Total Sales] twice; with VAR the formula is shorter, faster and easier to check.
A variable name cannot contain spaces and cannot match a table name (so do not call a variable Sales).
Amount is already net of discounts while COGS is not, so promotions also lower the margin. That is exactly what the owner wants to see.

Data model: kopi· table Sales

Margin Kotor =

Loading the DAX editor…

Write the DAX expression, then press Check answer.

Validation result