SUM vs SUMX in DAX: How Iterator Functions Work
· 6 min read
SUM adds up one column. SUMX walks through a table row by row, evaluates an expression for each row, and then adds the results. Use SUMX whenever the number you want does not exist as a single column yet, for example quantity times price.
Quantity Sold = SUM ( Sales[Quantity] )
Revenue =
SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] * (1 - Sales[DiscountPct]) )SUM ( Sales[Quantity] ) is in fact shorthand for SUMX ( Sales, Sales[Quantity] ). The difference only matters when the expression multiplies or divides columns, and then it matters a lot. The examples below use Brightline Supply Co. order lines.
The example data
Brightline's Sales fact table has one row per order line, related to Products, Customers and Date. Five lines from four orders:
| OrderID | Product | Quantity | UnitPrice | DiscountPct |
|---|---|---|---|---|
| 50001 | Espresso Beans 1kg | 4 | 24.00 | 0.00 |
| 50001 | Tea Assortment (200) | 2 | 19.90 | 0.00 |
| 50002 | Copy Paper A4 (500) | 10 | 5.50 | 0.10 |
| 50003 | Paper Towels (24) | 3 | 25.00 | 0.00 |
| 50004 | Toner Cartridge Black | 2 | 59.00 | 0.05 |
Line revenue is quantity × price × (1 − discount): 96.00, 39.80, 49.50, 75.00 and 112.10. Total revenue is 372.40.
Sum vs SUMX: same question, different answers
Here is the SUMX measure next to a version written with SUM, which is what you get if you translate "quantity times price" word for word:
Revenue =
SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] * (1 - Sales[DiscountPct]) )
Revenue (wrong) =
SUM ( Sales[Quantity] ) * SUM ( Sales[UnitPrice] ) * (1 - SUM ( Sales[DiscountPct] ))| OrderID | Revenue (SUMX) | Revenue (wrong) |
|---|---|---|
| 50001 | 135.80 | 263.40 |
| 50002 | 49.50 | 49.50 |
| 50003 | 75.00 | 75.00 |
| 50004 | 112.10 | 112.10 |
| Total | 372.40 | 2,381.19 |
The wrong version matches on every order with a single line, which is why it survives a quick glance. On order 50001 it multiplies the total quantity (6) by the sum of two prices (43.90). On the total row it multiplies 21 units by 133.40 in prices and by (1 − 0.15). That number means nothing.
The rule: multiply first, then add. SUM adds first. SUMX does the arithmetic on each row and adds at the end.
In SQL you already do this without thinking: SUM(quantity * unit_price * (1 - discount_pct)) computes per row inside the aggregate. SUMX is the DAX way to write that. See DAX vs SQL for more translations.
How SUMX works
SUMX ( <table>, <expression> ) has two arguments:
- The table to iterate. DAX takes this table as filtered by the current filter context. In the 50001 row, only the two lines of order 50001 are visible.
- The expression. DAX evaluates it once per row, in a row context, so
Sales[Quantity]means "the quantity on this row".
Then it sums the results. You can iterate any table expression, not just a fact table: VALUES ( Sales[OrderID] ), Customers, or FILTER ( Sales, ... ).
SUMX can also reach other tables. RELATED follows the relationship from the current Sales row to Products:
Cost =
SUMX ( Sales, Sales[Quantity] * RELATED ( Products[UnitCost] ) )More on that in the RELATED function.
DAX iterator functions
Every aggregation has an X version that iterates:
| Function | Returns |
|---|---|
SUMX | Sum of the expression over the rows |
AVERAGEX | Average of the expression |
MINX, MAXX | Smallest or largest value of the expression |
COUNTX | Number of rows where the expression is not blank |
RANKX | Rank of the current item among the rows of a table |
CONCATENATEX | The expression values joined into one text |
FILTER, ADDCOLUMNS and GENERATE are also iterators: they create a row context, even though they return tables.
AVERAGEX: average order value
A frequent request: "What is our average order value?" There are two different averages hiding in the question:
Average Line Value =
AVERAGEX ( Sales, Sales[Quantity] * Sales[UnitPrice] * (1 - Sales[DiscountPct]) )
Average Order Value =
AVERAGEX ( VALUES ( Sales[OrderID] ), [Revenue] )| Measure | Result | Calculation |
|---|---|---|
| Average Line Value | 74.48 | 372.40 / 5 lines |
| Average Order Value | 93.10 | 372.40 / 4 orders |
The second version iterates the list of order IDs. For each one, [Revenue] is evaluated for that order alone. That works because a measure reference inside an iterator triggers context transition: the current row of the iteration becomes a filter, as if the measure were wrapped in CALCULATE. Order 50001 contributes 135.80, not 96.00 and 39.80 separately.
DIVIDE ( [Revenue], DISTINCTCOUNT ( Sales[OrderID] ) ) gives the same 93.10 and is easy to read. AVERAGEX is the pattern to know, because it generalises to "average per customer", "average per month" and so on.
MAXX and RANKX
The same pattern answers "what was our biggest order?":
Largest Order =
MAXX ( VALUES ( Sales[OrderID] ), [Revenue] )Result: 135.80 (order 50001). And to rank customers by revenue:
Customer Rank =
RANKX ( ALL ( Customers[CustomerName] ), [Revenue] )ALL matters here: without it, each row of the visual only sees its own customer, and every customer ranks 1.
When SUM is enough
If the column already holds the number you want, SUM is clearer and equally fast. A stored Line Revenue column with SUM ( Sales[Line Revenue] ) returns the same 372.40 as the SUMX measure. The trade-off is memory: the column is stored for every row. See measure vs calculated column for when that is worth it.
Common mistakes
SUM of columns multiplied together
The table above. AI assistants produce SUM ( Sales[Quantity] ) * SUM ( Sales[UnitPrice] ) often, especially when you describe the measure in words. How to spot it: put the order ID (or any key with several lines per value) on the rows and compare against a hand calculation for one multi-line order. If single-line rows match and multi-line rows do not, it is this bug.
An aggregate inside the iterator
A subtler AI version:
Revenue (wrong) =
SUMX ( Sales, SUM ( Sales[Quantity] ) * Sales[UnitPrice] * (1 - Sales[DiscountPct]) )SUM ( Sales[Quantity] ) does not see the row context, so it returns the total quantity of the current filter context on every row. Order 50001 shows 263.40 again, and the grand total becomes 2,727.90. Inside an iterator, refer to the row's columns directly (Sales[Quantity]), not to an aggregate of them.
AVERAGE where AVERAGEX over orders was meant
AVERAGE ( Sales[Line Revenue] ) averages lines, not orders. Here it returns 74.48 when the business question was average order value, 93.10. Check what one row of the table you are averaging represents.
Iterating a huge table when a column exists
SUMX ( Sales, Sales[Line Revenue] ) is the same as SUM ( Sales[Line Revenue] ). Not wrong, just noise. Keep SUMX for real row-by-row expressions.
FAQ
What is the difference between SUM and SUMX in Power BI?
SUM adds the values of one column in the current filter context. SUMX evaluates an expression for each row of a table and adds the results. Use SUMX when the value has to be computed per row first.
When should I use SUMX instead of SUM?
When the expression involves more than one column (quantity × price), a related table (RELATED), or a measure evaluated per item (revenue per order, per customer).
Is SUMX slower than SUM?
For simple expressions the engine optimises SUMX well, and the difference is small. Iterators get slow when the expression calls measures on a large table, because each row triggers context transition.
What does AVERAGEX do in DAX?
It evaluates an expression for each row of a table and returns the average of the results. AVERAGEX ( VALUES ( Sales[OrderID] ), [Revenue] ) returns average revenue per order.
What are iterator functions in DAX?
Functions that loop over a table row by row and evaluate an expression in row context: SUMX, AVERAGEX, MINX, MAXX, COUNTX, RANKX, CONCATENATEX, plus table functions like FILTER and ADDCOLUMNS.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Sales before discounts” →