Common DAX Mistakes: SUM vs SUMX, and When Each Is Right

Of all the DAX mistakes I see in cohort after cohort, one shows up more than any other: reaching for SUM when the calculation actually needs SUMX, or the reverse — wrapping a perfectly simple total in an iterator it doesn't need. Both versions of the mistake come from the same gap: not knowing what SUMX is actually doing differently from SUM, only that it's "the row-by-row one."

This post is the explanation I give candidates the first time they hit a measure that returns the wrong number even though every individual piece of the formula looks correct.

SUM: aggregate a column, full stop

SUM does exactly one thing — it adds up every value in a single column, respecting whatever filter context is currently active.

Total Sales = SUM(Sales[SalesAmount])

If Sales[SalesAmount] already holds the number you want summed — one row per transaction, one amount per transaction — SUM is the right and complete answer. There's no row-by-row logic to apply first; the column already contains the values you're adding. Reaching for anything more complicated here is the "using SUMX when you don't need to" version of the mistake, and it's mostly a performance and readability issue rather than a correctness one — SUMX(Sales, Sales[SalesAmount]) returns the same number as SUM(Sales[SalesAmount]), just slower and harder to read, because it's iterating row by row to do what SUM already does directly.

SUMX: when the value to sum doesn't exist yet

SUMX earns its place the moment the number you need isn't sitting in a column — it has to be calculated for each row before it can be added up.

Total Revenue =
SUMX(
    Sales,
    Sales[Quantity] * Sales[UnitPrice]
)

Here, no column in the Sales table holds "revenue." SUMX iterates the Sales table one row at a time, evaluates Sales[Quantity] * Sales[UnitPrice] in the row context of that row, and adds the result to a running total. This is the defining trait of every X-suffixed DAX function — SUMX, AVERAGEX, MAXX, COUNTX — they all take a table as the first argument and an expression to evaluate per row as the second, then aggregate the per-row results.

The mistake that actually costs marks: SUM of a ratio

The version of this mistake that shows up constantly — in real reports and in PL-300 case studies — isn't "which function is faster." It's using SUM on a column that shouldn't be summed at all, because the column itself is already a ratio or an average.

-- WRONG: this sums a column of percentages, which is meaningless
Bad Discount Total = SUM(Sales[DiscountPercent])

-- RIGHT: weight each row's discount by its own sales amount, then aggregate
Weighted Avg Discount =
DIVIDE(
    SUMX(Sales, Sales[SalesAmount] * Sales[DiscountPercent]),
    SUM(Sales[SalesAmount])
)

Adding up a column of percentages produces a number with no real meaning — you can't sum "10%, 15%, 20%" and get anything usable. The correct approach depends on what question you're actually answering, but a common one is a sales-weighted average: multiply each row's discount by that row's sales amount using SUMX, sum those weighted values, then divide by total sales with DIVIDE. This is the pattern PL-300 case studies test when they describe a column that "looks summable" — a percentage, a rate, a per-unit price — and ask you to produce a meaningful total or average from it. If the column is a ratio or a rate, summing it directly is almost always wrong; you need to reconstruct the underlying quantities first.

A second common trap: SUMX with a measure inside it

Total Sales = SUM(Sales[SalesAmount])

-- Redundant, not wrong, but worth understanding why
Total Sales Again =
SUMX(
    Sales,
    [Total Sales]
)

This one deserves care because it's subtle rather than obviously wrong. Referencing a measure — [Total Sales] — inside SUMX's row context triggers context transition: DAX converts the current row into an equivalent single-row filter and re-evaluates [Total Sales] under that filter, which (for a simple sum) gives back that row's own SalesAmount. Summed across every row, you get the same grand total as SUM(Sales[SalesAmount]) — just via a much more expensive path, because every single row triggers its own filter-context evaluation instead of one direct column scan. It happens to produce the right number here, which is exactly what makes it dangerous: the same pattern with a more complex measure (one involving its own CALCULATE or filters) can produce a genuinely different — and wrong — result, because context transition changes what the inner measure sees. If you find yourself wrapping a measure reference in SUMX, stop and ask whether you actually need row-by-row iteration or whether a direct SUM (or the measure itself) already does the job.

A decision rule you can actually use

When you're staring at a calculation and unsure which to use, ask one question: does the value I need to sum already exist as a column, or do I need to calculate it per row first?

• Value already exists in a column → SUM.
• Value has to be computed from two or more columns, or from a measure that needs to be evaluated per row → SUMX.
• The column is a rate, ratio, or percentage → neither SUM nor SUMX on that column directly; reconstruct the weighted total first, the way the discount example above does.

This same rule generalizes to the rest of the X-suffixed family. AVERAGE on an existing column, AVERAGEX when you need to average a per-row calculation. MAX on an existing column, MAXX when the value to compare is computed per row. Once you can answer "does this value already exist, or do I have to build it row by row," the choice between the plain function and its iterator stops being a guess.

Why this matters beyond the exam

Performance is the other reason this distinction matters once you're building real models, not just passing an exam. SUM scans a single column directly using Power BI's VertiPaq storage engine, which is extremely fast. SUMX has to materialize a row context for every row of the table it's iterating and evaluate an expression in each one — on a small table that's invisible, but on a fact table with tens of millions of rows, an unnecessary SUMX where a plain SUM would have worked is a measurable difference in report responsiveness. Knowing when you actually need the iterator, rather than reaching for it out of habit, is a habit worth building early.