DAX I: Measures

Why this matters

Open your A04 star and look at the sales table: Quantity, UnitPrice, DiscountPct — and no Revenue column. That is not an oversight in the data; it is how transactional systems actually export, and it has been true of this table since Lesson 3. The Lesson 1 extract had revenue pre-computed for you, a training-wheels decision someone made upstream. Nobody is upstream anymore. You are upstream.

The VP wants total revenue on a card. This lesson gives you two ways to build it, makes you fluent in both, and then teaches the distinction that — ask any Power BI professional — is the single most confused pair of concepts in the product: the calculated column and the measure. Both are written in DAX, the formula language of Power BI models. Both can produce that card. They are not the same thing, and knowing why is what Module 2 is for.

Road one: the calculated column

In Table view, on the sales table: New column, then:

Line Revenue = Sales[Quantity] * Sales[UnitPrice] * (1 - Sales[DiscountPct])

Power BI computes this once per row — 165,042 times — and stores every result in the table like any other column. The mechanism doing the per-row work has a name you should learn now: row context. Inside a calculated column, DAX stands on one row at a time; Sales[Quantity] means this row's quantity. The formula never says "for each row" — the row context is the deal a calculated column signs before the first character is typed.

Check the new column's box and a card reads $66.81M — precisely $66,813,563.97, the verified all-time total. It works. Hold that thought while we look at what it cost: 165,042 stored values, recomputed at every refresh, spending memory to remember what could have been computed on demand.

Implicit versus explicit — name your numbers

That card is powered by an implicit measure — Power BI's automatic SUM, the same guess you met in Lesson 1. It guessed right today. Professionals still replace it, and here is the move: New measure:

Total Revenue = SUM ( Sales[Line Revenue] )

Why bother, when the number is identical? Because an explicit measure has a name other measures can reference, a format you set once ($ with two decimals, or $#,##0,, "M"), and a definition that lives in exactly one place. When the VP asks "what exactly is in your revenue number?", an explicit measure answers with its formula. An implicit one answers "whatever the default did." From this lesson forward, the course rule is: every number that appears on a report has a named measure behind it.

Road two: the measure that needs no column

Now delete nothing, but watch this. The entire Line Revenue column can be skipped:

Total Revenue = SUMX (
    Sales,
    Sales[Quantity] * Sales[UnitPrice] * (1 - Sales[DiscountPct])
)

SUMX is an iterator: it walks the sales table row by row, evaluates the expression in a row context it creates on the fly, and sums the results — arriving at the same $66,813,563.97 without storing a single intermediate value. The X-functions (SUMX, AVERAGEX, COUNTX…) are how measures borrow the calculated column's per-row power, use it, and throw the scaffolding away.

Crossing the star: RELATED

The VP's second question was inevitable: "fine, revenue — but what did it cost us?" Unit costs live in the products table; quantities live in sales. Row context, standing on a sales row, reaches across the relationship with RELATED:

Total Cost = SUMX (
    Sales,
    Sales[Quantity] * RELATED ( Products[UnitCost] )
)

RELATED follows the many-to-one relationship from the sales row to its product row and fetches the cost — the model you built in Lesson 4 doing load-bearing work inside a formula. Verified: $28,099,192.18. And now the payoff of naming things — measures build on measures:

Gross Profit    = [Total Revenue] - [Total Cost]
Gross Margin %  = DIVIDE ( [Gross Profit], [Total Revenue] )

$38,714,371.79, and 57.9%. Note the square brackets with no table name — the convention that signals "this is a measure, not a column." Note also DIVIDE instead of the slash: it returns blank instead of an error when the denominator is zero — and a filtered-to-nothing visual cell hits zero denominators constantly. The slash works until the day it doesn't; DIVIDE is the habit.

One more, closing Lesson 4's loop — the return rate the star made possible, now written properly as a measure:

Return Rate = DIVIDE (
    SUM ( Returns[QuantityReturned] ),
    SUM ( Sales[Quantity] )
)

Verified: 3.21% — one measure reading two fact tables through the shared dimensions, recomputing honestly for any category, region, or year a reader selects.

The ambiguity trap: which "average revenue"?

The VP says: "add average revenue to the dashboard." A reasonable analyst hears that and builds — one of two completely different numbers:

Nearly double, one against the other — because an order averages about two lines. Neither is wrong; they are answers to different questions, and "average revenue" did not say which. The per-line figure describes what a typical cart item is worth; the per-order figure (usually called average order value) describes what a typical checkout is worth, and it is almost always what a retail VP means. The analyst's job is the sentence before the formula: "Average per order, or per line? They differ by almost 2× in our data." Asking it is not pedantry — it is the difference between the dashboard agreeing with finance's numbers or not. (You met this pattern with units-versus-dollars in Lesson 1. It never stops appearing.)

A preview with a name attached

Put [Total Revenue] in a table visual against Category, and the same measure shows five different values — $24.80M for Furniture, $5.02M for Decor. Nobody wrote five formulas. The measure recomputed inside each cell's filter context — the set of filters each visual cell applies before the measure runs. You have watched filter context work since Lesson 1's first slicer click; now it has a name, and next lesson it gets the full treatment — including CALCULATE, the function that edits it, and with it percent-of-total, year-over-year, and every comparison a dashboard lives on.

Common mistakes

• Building a calculated column for every number. Columns spend memory per row and freeze at refresh. If it aggregates, it is a measure.
• Leaving implicit measures on a shipped report. Unnamed, unformatted, undocumented. Every reported number gets an explicit measure — the course rule from here on.
• SUM where the math is per-row. SUM(Quantity) * SUM(UnitPrice) multiplies two grand totals — a spectacular, plausible-looking wrong number. Per-row arithmetic needs an iterator: SUMX.
• The bare slash. [A] / [B] errors the first time a visual cell's filter context empties the denominator. DIVIDE returns a quiet blank.
• Accepting ambiguous requests. "Average revenue" is two different numbers ($404.83 vs $781.10 here). Resolve the words before writing the formula.

Summary