Lecture video · L05 DAX I: Measures

[Slide 1] Open your star from last week and look closely at the sales table. Quantity, unit price, discount percent — and no revenue column. That is not a mistake in the data; it is how transactional systems actually export. The Lesson One file had revenue pre-computed for you — training wheels somebody bolted on upstream. Tonight, nobody is upstream. You are upstream. The vice president wants total revenue on a card, and by the end of this session you will have built it two different ways, met the formula language called DAX, and learned the distinction professionals call the most confused pair of concepts in this product: the calculated column and the measure.

[Slide 2] Road one. In Table view, on the sales table, we create a new column: Line Revenue equals quantity times unit price times one minus the discount. Power BI computes this once per row — a hundred sixty-five thousand times — and stores every result in the table. Notice the formula never says for each row; it doesn't have to. Inside a calculated column, DAX stands on one row at a time, so Sales Quantity means this row's quantity. That mechanism is called row context, and it is the deal every calculated column signs before the first character is typed. Check the new column's box and the card reads sixty-six point eight one million dollars — precisely sixty-six million, eight hundred thirteen thousand, five hundred sixty-three dollars and ninety-seven cents. Notice that is three times the figure from week one, because this model holds all three and a half years, not just twenty twenty-five. It works. Now let us talk about what it cost.

[Slide 3] The card you just built ran on an implicit measure — the automatic SUM you met in week one. It guessed right. Professionals replace it anyway, and the replacement takes ten seconds: new measure, Total Revenue equals SUM of Line Revenue. Same number. So why bother? Three reasons that will each save you an afternoon someday. An explicit measure has a name that other measures can reference. It has a format you set once — currency, two decimals — instead of forty times across forty visuals. And it has a definition that lives in exactly one place, so when the VP asks what exactly is in your revenue number, you answer with a formula instead of a shrug. From tonight forward, the course rule is simple: every number that appears on a report has an explicit, named, formatted measure behind it. Implicit sums are for exploring, never for shipping.

[Slide 4] Road two makes the column optional. SUMX takes a table and an expression, walks the table row by row, evaluates the expression in a row context it creates on the fly, sums the results, and throws the scaffolding away. Same sixty-six point eight million, zero stored values. The X functions — SUMX, AVERAGEX, COUNTX — are how measures borrow the calculated column's per-row power without paying its storage bill. So which road do you take? Here is the decision rule, and it is worth memorizing: if the thing is a property of the row — a price band, a region tag, something you slice by — it is a column. If it is an answer to a question — total revenue, return rate, average order — it is a measure, always, because answers must recompute for whatever the reader filters. When both would work, prefer the measure.

[Slide 5] The VP's second question was inevitable: 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 a function called RELATED — it follows the many-to-one path you built last week and fetches that product's cost. Twenty-eight million, ninety-nine thousand, one hundred ninety-two dollars. And now watch measures build on measures: Gross Profit is Total Revenue minus Total Cost — thirty-eight point seven million — and Gross Margin percent is DIVIDE of profit by revenue: fifty-seven point nine percent. Two things to notice. Square brackets with no table name mean measure — that is the convention. And DIVIDE instead of the slash: DIVIDE returns a quiet blank when the denominator is zero, and filtered visual cells hit zero denominators constantly. The slash works right up until the day it doesn't.

[Slide 6] Discussion time, and this one has teeth. The VP asks for average revenue on the dashboard. Take two minutes with a neighbor and decide what you would actually build — write the formula. Here is the trap: average of what? AVERAGE of Line Revenue averages the hundred sixty-five thousand order lines — four hundred four dollars and eighty-three cents. Total revenue divided by DISTINCTCOUNT of Order ID averages the eighty-five thousand orders — seven hundred eighty-one dollars and ten cents. 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 analyst's job is one sentence before the formula: per order, or per line? They differ by two X in our data. You met this exact shape in week one — units versus dollars — and here it is again wearing DAX. It never stops appearing.

[Slide 7] One last idea, and it is next week's whole show, so consider this the trailer. Put Total Revenue in a table against Category and the same measure shows five different values — twenty-four point eight million for Furniture down to five million 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 your first slicer click in week one; tonight it finally has its name. Next week you meet CALCULATE, the function that edits filter context deliberately — and with it percent of total, year over year, and every comparison a real dashboard lives on. Bring this week's measure family; all of it gets reused.

[Slide 8] The week in one breath. Calculated columns store a value on every row, computed in row context; measures store a definition that computes on demand in filter context — property of a row is a column, answer to a question is a measure. Every reported number now gets an explicit, named, formatted measure; that rule is permanent. SUMX gives measures per-row arithmetic without storage, RELATED reaches across the relationships you built last week, and DIVIDE never divides by zero angrily. Measures build on measures — you went from revenue to cost to profit to margin in four short formulas. And average revenue turned out to be two numbers nearly double apart, resolved by words before DAX. This week: the practice trio runs from rehearsal to putting the trade-discount program itself on trial. Assignment A-zero-five grades your measure family by exact name and verified value — the purest version of the contract this course grades on. Next week: CALCULATE, filter context, and the date table that finally lets finance's targets join the star.