In DAX, why is SUMX(Sales, Sales[Qty] * Sales[Price]) not SUM(Sales[Qty]) * SUM(Sales[Price])?
answer
- arithmetic does not distribute over addition
- two lines, different prices, do the maths by hand
- the X suffix means one row at a time
- think about the grain the multiplication happens at
basics
~20 sSUMX multiplies quantity by price inside each row and then adds the results, so every row uses its own price. Multiplying two SUMs multiplies a total quantity by a total price, which is a number no transaction ever had.
solid answer
~50 s`SUMX` is an iterator: it walks the rows of the table given as its first argument, evaluates the expression once per row under that row context, and sums the results. So `SUMX ( Sales, Sales[Qty] * Sales[Price] )` computes line revenue per line and totals it. `SUM ( Sales[Qty] ) * SUM ( Sales[Price] )` aggregates each column across all rows first and multiplies the two totals — mathematically a different operation, and one with no business meaning once prices vary between rows. They coincide only in the degenerate case of a single row, or when one factor is constant across every row. The general rule: any calculation where the arithmetic has to happen at row grain before aggregation needs an iterator — SUMX, AVERAGEX, MINX, MAXX — and doing it with a calculated column instead materialises a column at refresh time that the iterator computes on the fly.
code
dax · 8 lines-- correct: multiply inside each row, then add
Revenue = SUMX ( Sales, Sales[Qty] * Sales[Price] )
-- wrong: totals multiplied together
Revenue Wrong = SUM ( Sales[Qty] ) * SUM ( Sales[Price] )
-- weighted average price, not the mean of the price column
Avg Price Paid = DIVIDE ( SUMX ( Sales, Sales[Qty] * Sales[Price] ), SUM ( Sales[Qty] ) )go deeper
Be able to write SUMX for line revenue and say in one sentence why multiplying two SUMs is wrong. A worked two-row example is the fastest way to prove you understand it.
Explain that iterators establish a row context per row and that SUM is SUMX over one column. Be ready to compare the iterator with a calculated column on refresh cost, memory and query time.
Show that you choose the table argument deliberately — averaging across customers versus across order lines is a business decision — and that you recognise context transition inside a fact-table iterator as the expensive pattern.
Own the definitional side: a weighted average, an average order value and an average per customer are three metrics that stakeholders all call 'average', and pinning which one the organisation reports is the actual deliverable.
## Aggregation does not distribute over multiplication This is arithmetic before it is DAX. Summing the products of pairs is not the product of the sums: `Σ(qᵢ × pᵢ) ≠ (Σqᵢ) × (Σpᵢ)`. Two lines — 2 units at $10 and 3 units at $100 — give real revenue of 20 + 300 = $320, while multiplying totals gives 5 × $110 = $550. The second number corresponds to nothing that happened. Every BI tool has a version of this bug; in DAX the fix is choosing an iterator. ## What an iterator does Functions ending in X — `SUMX`, `AVERAGEX`, `MINX`, `MAXX`, `COUNTX`, `PRODUCTX`, `RANKX`, and structurally also `FILTER` and `ADDCOLUMNS` — take a table as the first argument and an expression as the second. They iterate the table row by row, establishing a **row context** for each row, evaluate the expression there, and aggregate the resulting scalars. That row context is what makes the bare references `Sales[Qty]` and `Sales[Price]` mean "this row's quantity and this row's price". ``` Revenue = SUMX ( Sales, Sales[Qty] * Sales[Price] ) ``` The table argument is not necessarily the physical table — it is any table expression evaluated in the current filter context, so `SUMX` over `Sales` in a matrix cell iterates only the rows visible in that cell. ## SUM is itself an iterator in disguise `SUM ( Sales[Amount] )` is internally equivalent to `SUMX ( Sales, Sales[Amount] )`. The single-column form exists because it is shorter and because the engine can often answer it from a compressed column scan. This equivalence is worth knowing because it explains why `SUM` cannot take an expression: the moment you need arithmetic across two columns, there is no single column to scan and you must reach for the X version. ## Iterator vs calculated column The alternative to `SUMX ( Sales, Sales[Qty] * Sales[Price] )` is a calculated column `Line Revenue = Sales[Qty] * Sales[Price]` followed by `SUM ( Sales[Line Revenue] )`. Both give the correct number. The difference is where the work happens: the calculated column is materialised at data-refresh time, occupies memory in the model and is compressed with the rest of the table; the iterator computes at query time and stores nothing. For a straight product of two columns on a large fact table the materialised column often compresses well and queries fast, while the iterator keeps the model smaller. The judgment call is real, but the correctness question — row grain before aggregation — is answered identically by both. ## AVERAGEX and the same trap The averaging version bites harder. `AVERAGE ( Sales[Price] )` is the mean of the price column: every line weighted equally regardless of quantity. What people usually want is the revenue-weighted average price, `DIVIDE ( SUMX ( Sales, Sales[Qty] * Sales[Price] ), SUM ( Sales[Qty] ) )`. And `AVERAGEX ( Customer, [Total Sales] )` is the average across customers, which is a completely different number from the average across order lines — the choice of the table argument decides the grain the average is taken at, and that is the part interviewers probe. ## The performance edge Iterators are not automatically slow: `SUMX` over columns of one table is typically resolved efficiently by the engine's storage layer. What is slow is an iterator whose expression forces **context transition** — most often a measure reference — over a large table. `SUMX ( Sales, [Total Sales] )` applies a filter per fact row, which is both semantically odd and expensive. `SUMX ( VALUES ( Customer[CustomerKey] ), [Total Sales] )` iterates a few thousand customers instead of millions of rows and is the pattern you actually want when the per-row expression is a measure. ## How to recognise the situation Ask one question of any calculation: does the arithmetic have to be done per row before anything is added up? Line revenue, weighted averages, per-row conditional amounts, anything comparing two columns of the same row — all yes, all iterators. Simple additive totals of a single column — no, plain `SUM` is clearer and faster to read.
- When are SUMX over an expression and a calculated column plus SUM equally correct, and how do you choose?Both give the same number for a row-level product like quantity times price. The calculated column materialises at refresh, costing model memory but compressing well and querying fast; the iterator computes at query time and stores nothing. Choose the column when the model is small relative to memory and the expression is reused everywhere, the iterator when you want to keep the model lean or the expression varies by measure.
- Why is AVERAGEX(Customer, [Total Sales]) different from AVERAGE over the fact table?The table argument sets the grain. AVERAGEX over Customer evaluates the measure once per customer — with context transition supplying that customer's filter — and averages across customers. An average over fact rows averages across order lines, so a customer with 200 small orders dominates it. Neither is wrong; they answer different questions and you must say which one the report means.
- When does an iterator become a performance problem?When the expression forces context transition over a large table — typically a measure reference inside SUMX over a fact table, which applies a filter per row. Iterating a small dimension or a VALUES list of keys instead of millions of fact rows usually fixes it. Straight column arithmetic inside SUMX is normally resolved efficiently and is not the thing to worry about.
It is the difference between totting up each receipt line and paying the sum, versus multiplying the total number of items you bought by the total of all the price tags — the second is a bigger number and nobody's bill.
saying these in an interview costs you the question
- Claiming SUM(a) * SUM(b) equals SUMX of the row product
- Thinking iterators are always slower than plain aggregates
- Using AVERAGE on a price column when a weighted average is meant
- Believing SUMX needs a calculated column to work
- Iterating the fact table with a measure reference for a per-entity calculation