Module 4: DAX basics · Lesson 4.3 · about 18 min
Iterators: SUMX and row context
SUMX, AVERAGEX, MAXX and friends go through a table one row at a time, work out an expression for each row, then combine the results. Watch it happen row by row and see why SUM × SUM is wrong.
Module 4 · about 18 minutes · read first: 4.1 and 3.4. You’ll be able to write iterator measures and explain row context.
1. Why this matters
Whenever a calculation must happen per row before adding up (price × quantity, cost × units, days between two dates), you need an iterator. Row context is also half of DAX’s mental model: understanding it now makes CALCULATE easy later.
2. The idea, step by step
Run it, click a category, then Show me what happened: you’ll see the filter context first, then SUMX going through the rows, the new column it works out for each one, and the total.
Write Revenue with SUMX: for every Sales row, Quantity × UnitPrice, added up. (It should equal SUM of Amount.)
Press Run to see the result.
SUMX(table, expression): SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]).
Revenue = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]) 3. The X family
| Function | For each row… then | Example |
|---|---|---|
SUMX(table, expr) |
adds | revenue, cost, weighted totals |
AVERAGEX(table, expr) |
averages | average sales per day (Lesson 5.5) |
MAXX, MINX |
takes the largest / smallest | biggest order line |
COUNTX(table, expr) |
counts non-blank results | lines with a value |
CONCATENATEX(table, expr, ", ") |
joins text | list of selected stores in a title |
The first argument can be any table: Sales, VALUES(Product[Product]), or a filtered table (Lesson 5.4).
Write Biggest Line: the largest single order line value (Quantity × UnitPrice), with MAXX.
Press Run to see the result.
Biggest Line = MAXX(Sales, Sales[Quantity] * Sales[UnitPrice]) 4. The classic mistake
Run both. Why is the second one wildly wrong? Click a row and use Show me what happened on each (change which is last to trace it).
Press Run to see the result.
Wrong adds all quantities, adds all prices, then multiplies the two big numbers. Every quantity gets paired with every price. Right multiplies each row’s own quantity by its own price first.
5. Row context
Inside an iterator (and in a calculated column), there is a current row, so Sales[Quantity] means “this row’s quantity”. That’s row context. A measure on its own has no current row, which is why Sales[Quantity] alone fails in a measure (Lesson 1.5). Two things row context does not do: it doesn’t filter other tables, and it doesn’t change what SUM sees. Lesson 5.5 shows how CALCULATE turns a row into a filter.
Principle
Principle: sum of products is not product of sums.
Try it: two lines, (q = 2, p = 10) and (q = 1, p = 50). Revenue = 2·10 + 1·50 = 70. Wrong formula: (2 + 1)(10 + 50) = 180.
Speed: iterators over millions of rows are fast when the expression only uses the row’s own columns. Avoid calling complex measures inside SUMX over a big fact table; iterate a smaller table (like VALUES of a dimension) instead.
6. Worked example
Total cost when the cost is stored per unit. Total Cost = SUMX(Sales, Sales[Quantity] * Sales[UnitCost]). For Northbay that’s about $229,000 against $466,000 of sales, a margin of about 51 %. Store the per-row result as a column only if you need to slice by it.
7. Practice
- Write
Average Line Valuewith AVERAGEX. Is it the same asDIVIDE([Total Sales], COUNTROWS(Sales))? - Write
Units at Full Price: the total quantity of lines with UnitPrice equal to the 2024 price. (Hint: FILTER inside SUMX, Lesson 5.4.) - What would
MAXX(Sales, Sales[Date])give?
Answers
AVERAGEX(Sales, Sales[Quantity] * Sales[UnitPrice]). Yes: the average of per-line values equals total ÷ number of lines.- One way:
SUMX(FILTER(Sales, Sales[UnitPrice] = RELATED(Product[UnitPrice])), Sales[Quantity])(RELATED is next lesson). - The latest sale date in the current filter context, the same as
MAX(Sales[Date]).
Check yourself
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. Northbay Goods is a made-up shop and its numbers are generated. Power BI changes every month: menu names can move, so check Microsoft Learn if a button is not where the lesson says. The practice editor runs in your browser; nothing you type is sent anywhere.