SUM vs SUMX in DAX: Why a Row-by-Row Calculation Needs an Iterator

1. The fork: is the number already in a column?

Before the explanation, one question worth answering from memory. Think of the last measure you wrote. Say whether the values it added up were already stored somewhere, or whether the model had to work each one out first.

That question decides which function you reach for, and it has two answers.

Answer one: the number is already in a column. Units, Revenue, Amount, Headcount. Somebody stored it, one value per row, and adding it up is the whole job. If that is true you want SUM, and you hand it the column.

Answer two: the number does not exist yet. Line revenue when the table only has units and a price. Margin when the cost lives on another table. Weighted score, extended amount, anything with an operator in the middle of it. If that is true, something has to visit each row, work the value out there, and only then add them up. That something is SUMX.

What decides between them is not how complicated the formula looks. It is whether you could point at a column in the Fields pane and say "that one". If you can, it is SUM. If you have to describe a calculation instead, it is SUMX.

It matters because picking the wrong one does not always produce an error. Sometimes it produces a number, and the next three sections are about what that number is and why it survives.

2. What SUM will and will not accept

Microsoft's reference page for SUM gives it one parameter, and describes it in six words: "The column that contains the numbers to sum." Not an expression. A column.

So this does not work, and it is the single most common thing a new Power BI user types:

Revenue = SUM ( Orders[Units] * Orders[UnitPrice] )

Power BI refuses it with a message that is unusually direct for a formula engine: The SUM function only accepts a column reference as an argument. Nothing is stored in the model called Units * UnitPrice, so there is no column to hand over.

That refusal is the helpful outcome. It stops you at the point of the mistake. The damaging version is the one that compiles, which is what happens when somebody works around the error by summing each column on its own:

Revenue = SUM ( Orders[Units] ) * SUM ( Orders[UnitPrice] )

Two columns, two legal SUMs, one multiplication. Power BI accepts it without complaint, because there is nothing wrong with it as DAX. It is only wrong as arithmetic.

3. The failure: 2,630 that reads 12,995

Take the first four orders and do it by hand. Four rows is small enough to check every step, and the shape of the error is identical at sixteen rows or sixteen million.

The right answer is 2,630, because 880 + 850 + 660 + 240 = 2,630. That is what SUMX returns: it works out each line, then adds the four results.

Now the version that compiles. SUM(Units) is 4 + 10 + 3 + 6 = 23. SUM(UnitPrice) is 220 + 85 + 220 + 40 = 565. Multiply them and you get 12,995, because 23 × 565 = 12,995.

Before reading on, say why 12,995 is so much bigger than 2,630. It is not rounding and it is not a doubled join. Say what those extra 10,365 are made of.

They are pairings that never happened. Multiplying two totals is the same as adding up every quantity crossed with every price: order 1001's four units at order 1002's price of 85, order 1004's six units at the desk price of 220, and so on through all sixteen combinations. Four rows produce 4 × 4 = 16 pairings. Only four of them are real, the ones where the quantity and the price came off the same line, and those four are the 2,630.

The gap is the other twelve: 12,995 minus 2,630 = 10,365.

Run the same thing on the full sixteen orders and the numbers get louder. SUMX returns 9,890. SUM(Units) is 101, SUM(UnitPrice) is 2,125, and 101 × 2,125 = 214,625. Sixteen rows means 16 × 16 = 256 pairings, and 240 of them are fiction.

Here is the working measure.

Total Revenue =
SUMX ( Orders, Orders[Units] * Orders[UnitPrice] )

Read it as two arguments doing two jobs. Orders is the table to walk. Orders[Units] * Orders[UnitPrice] is the calculation to run while standing on each row of it. SUMX adds the sixteen results.

4. Why nobody catches it: slice to one row and both agree

Before the explanation, a prediction. You have a table visual showing revenue, and you drill all the way down to a single order to check the measure. Say whether the broken version and the working version show the same figure in that cell.

They do, exactly, and this is the part worth carrying away from the page.

Filter the visual to order 1001 alone. SUMX walks one row and returns 4 × 220 = 880. The broken measure computes SUM(Units) over that one row, which is 4, and SUM(UnitPrice) over that one row, which is 220, then multiplies: 4 × 220 = 880. Identical.

That is not a coincidence about this fixture. With one row there is only one pairing available, so "every pairing" and "the real pairing" are the same set. The two measures cannot disagree.

The lamp row needs its own line, because it is the one that shows where the agreement stops. Lamps are four orders of 6, 12, 7 and 15 units, all at 40. SUMX gives 240 + 480 + 280 + 600 = 1,600. The broken measure gives SUM(Units) = 40 and SUM(UnitPrice) = 160, and 40 × 160 = 6,400. Four times too big, because four rows means sixteen pairings and only four are real.

So the test that proves the measure works is the test that cannot fail. A single-row check passes on a measure that is wrong by a factor of sixteen at the total. This is the same shape as the Excel fill whose first row is right in both the working and the broken version, and it is why the habit at the end of this page is about totals rather than about being careful.

5. What SUMX is actually doing on each row

Microsoft calls SUMX an iterator function, and defines the category in one sentence: "A DAX function that enumerates all rows of a given table and evaluate a given expression for each row."

The word that matters underneath that is row context. Row context is the model knowing which single row it is standing on, which is what makes Orders[Units] mean a number rather than a whole column. Microsoft's glossary puts it plainly: row context "represents the 'current row', and is used to evaluate calculated column formulas and expressions used by table iterators."

So the sequence inside SUMX(Orders, Orders[Units] * Orders[UnitPrice]) is:

1. Take the rows of Orders that survived the filters on the visual.
2. Stand on the first one. Orders[Units] is 4 and Orders[UnitPrice] is 220, so the expression is 880.
3. Move to the next. Repeat. Sixteen times.
4. Add up the sixteen results and return one number.

Nothing is stored. There is no revenue column at the end of it, which is the difference between this and a calculated column. A calculated column would compute 880 once, save it in the file, and use memory forever. SUMX computes it when a visual asks and throws it away.

One thing to know rather than discover: a row context does not filter anything. Standing on row 1001 does not restrict other tables to that order. Referencing a measure inside the iterator changes that, and it changes it silently, which is the trapdoor covered in CALCULATE and filter context. Read that one before you write SUMX(Orders, [Some Measure]).

Now picture this running on a table you actually own. Take your own fact table, find one number on a report that had to be worked out rather than looked up, and say out loud which table SUMX would walk and what the expression on each row would be. Two pieces, that is the whole call.

6. The rest of the X family

SUMX is not special. Every aggregation in DAX has an X twin, and they all take the same two arguments in the same order: a table, then an expression to evaluate on each row of it. Learning SUMX teaches you all of them at once.

CONCATENATEX is the one worth trying first, because it makes the row-by-row idea visible. It takes an optional third argument, the separator, and it is how a report shows "Desk, Chair, Lamp" in a title instead of a number.

AVERAGEX has one behavior worth knowing before it surprises you in a matrix. Microsoft states it directly: "When there are no rows to aggregate, the function returns a blank. When there are rows, but none of them meet the specified criteria, then the function returns 0." A blank cell and a zero cell mean different things, and only one of them draws a bar.

7. The wrong number that looks right: an average of percentages

Before this one: a report needs the overall margin percentage. Somebody writes a margin percent for each order and averages it. Say whether that gives the same answer as total margin divided by total revenue.

It does not, and unlike the revenue example the gap here is small enough to ship.

Cost lives on Products, not on Orders, so margin is a number that exists nowhere and has to be built. RELATED is what lets a row of Orders read its own product's row on the other side of the relationship.

Total Margin =
SUMX (
    Orders,
    Orders[Units] * ( Orders[UnitPrice] - RELATED ( Products[Cost] ) )
)

Per unit that is 220 - 140 = 80 on a desk, 85 - 52 = 33 on a chair, and 40 - 22 = 18 on a lamp. Across the sixteen orders there are 23 desk units, 38 chair units and 40 lamp units, so total margin is 23 × 80 + 38 × 33 + 40 × 18 = 1,840 + 1,254 + 720 = 3,814.

Revenue is 9,890. So the blended margin is 3,814 / 9,890 = 38.56%.

Now the version that averages. Every desk order runs at 80 / 220 = 36.36%, every chair at 33 / 85 = 38.82%, every lamp at 18 / 40 = 45.00%, whatever the quantity. There are seven desk orders, five chair orders and four lamp orders, so:

Average Margin % =
AVERAGEX ( Orders, DIVIDE ( [Order Margin], [Order Revenue] ) )

39.29% against a true 38.56%. Wrong by 0.73 percentage points, and entirely believable in a card on a dashboard.

The reason is worth saying in one line, because it is the whole class of error. The average gives every order one vote. Lamps are 4 of the 16 orders, so they carry 25% of the vote, but they are only 1,600 of the 9,890 in revenue, which is 16.18%. Lamps also have the highest margin percentage, so overweighting them pulls the average up.

A ratio of totals is not the total of ratios. The fix is to divide once, at the end:

Margin % = DIVIDE ( [Total Margin], [Total Revenue] )

The rule that comes out of this: SUMX the numerator, SUMX the denominator, and divide those two. Never average a percentage that was already a ratio, unless somebody has told you in writing that they want each order to count equally.

8. When SUM is the correct answer

Nothing above is an argument for using iterators everywhere. Microsoft's own guidance, on the SUMX page itself, is the opposite: "If you do not need to filter the column, use the SUM function."

SUM is right whenever the value is already sitting in the column, which covers most of a normal model. SUM(Orders[Units]) is 101 and there is nothing an iterator would add to that. A fact table that stores an extended amount per line should be summed, not re-derived.

There is a second case worth naming, because it looks like laziness and is not. If your fact table already has a stored revenue column, using it is more accurate than recomputing units times price, because the stored value is what was actually invoiced. Rounding, discounts and price overrides all live in that stored number and none of them are in the multiplication.

And if you find yourself writing SUMX(Orders, Orders[Units]), with a bare column as the expression and no calculation in it, that is a SUM with extra syntax. Write the SUM.