Aggregators and Iterators
Arjun wants a revenue measure that does not depend on the Line Revenue column, so the model can drop that column one day. His first attempt, SUM ( Sales[Qty] ) * SUM ( Sales[Price] ), returns 3,600 — six times the real 600. Nothing is wrong with SUM. The mistake is in when the multiplication happens.
The rule
Aggregators total one column; iterators calculate row by row, then total.
Aggregators take one column and return one value. Over the six Sales rows:
| Formula | Result |
|---|---|
SUM ( Sales[Qty] ) |
20 |
AVERAGE ( Sales[Price] ) |
30 |
MIN ( Sales[Price] ) |
20 |
MAX ( Sales[Qty] ) |
5 |
COUNT ( Sales[OrderID] ) |
6 |
DISTINCTCOUNT ( Sales[ProductID] ) |
3 |
COUNTROWS ( Sales ) |
6 — this one takes a whole table |
Iterators end in X: SUMX, AVERAGEX, MINX, MAXX, COUNTX. They take a table and an expression. They walk the table one row at a time, evaluate the expression for that row — with row context, just like a calculated column — and then aggregate the results.
Total Revenue = SUMX ( Sales, Sales[Qty] * Sales[Price] )Row by row: 60 + 40 + 80 + 120 + 100 + 200 = 600. Nothing is stored; the per-row results exist only while the measure runs.
Two more from the same family:
AVERAGEX ( Sales, Sales[Qty] * Sales[Price] )= 600 / 6 = 100, the average line revenue.MAXX ( Sales, Sales[Qty] * Sales[Price] )= 200, the biggest line (order 1006).
The table argument can itself be filtered. FILTER ( Sales, Sales[Qty] >= 4 ) returns just the rows with Qty 4 or more, so SUMX ( FILTER ( Sales, Sales[Qty] >= 4 ), Sales[Qty] * Sales[Price] ) adds 120 + 100 + 200 = 420.
In fact SUM ( Sales[Qty] ) is shorthand for SUMX ( Sales, Sales[Qty] ). An aggregator is simply an iterator whose expression is a single column.
The shortcut
Look at what you want to total. If it combines two or more columns of the same row, you need an X function. If it is one column, a plain aggregator does the job.
Filter context
In the Pune cell, only orders 1001, 1003 and 1005 are visible.
Drills
Answer fast — trust the rule.
What does SUMX ( Sales, Sales[Qty] * Sales[Price] ) return for the whole table?
What does SUM ( Sales[Qty] ) * SUM ( Sales[Price] ) return for the whole table?
What does AVERAGEX ( Sales, Sales[Qty] * Sales[Price] ) return?
What does DISTINCTCOUNT ( Sales[ProductID] ) return?
What does SUMX ( FILTER ( Sales, Sales[Qty] >= 4 ), Sales[Qty] * Sales[Price] ) return?
Remember
- Aggregators total one column; iterators calculate row by row, then total.
- Two columns combined in one row → an X function.
- Trap: multiplying totals, as in SUM times SUM, is not totalling the products.