DAX SUMX Function

Calculate expressions row-by-row before summing up results.

Syntax
SUMX( Table, Expression )

Overview

SUMX iterates over a table, creates a row context for each individual row, computes an expression, and sums the results.

SUMX is an iterator function (indicated by the 'X' suffix). It solves the common scenario where an analytical metric cannot be calculated as a single physical column, such as multiplying quantity by unit price across related dimension tables.

Total Sales Margin = 
SUMX(
    Sales,
    Sales[Revenue] - Sales[Cost]
)

When to use SUMX

  • Per-row arithmetic before summing, such as Quantity × Unit Price when there is no line-total column.
  • Summing a measure over the rows of a dimension, for example the total of each customer's capped bonus.
  • Weighted calculations that need values from several columns of the same row.

Worked examples

Line totals

Revenue =
SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )

Simple column arithmetic inside SUMX is handled efficiently by the storage engine. If this calculation is used everywhere, consider storing a line-amount column in the source and using SUM.

Summing a capped value per customer

Capped Bonus =
SUMX (
    VALUES ( Customer[Customer ID] ),
    MIN ( [Customer Bonus], 500 )
)

The cap must be applied per customer and then summed, which a single aggregation cannot express. Iterating the customer dimension keeps the number of context transitions to the number of customers.

Common mistakes

  • Using SUMX(Table, Table[Column]) when simple SUM(Table[Column]) would be much faster.
  • Referencing a measure inside SUMX without realizing it triggers Context Transition.
  • Iterating over large fact tables when a pre-aggregated dimension could be used.

Performance notes

SUMX with plain column arithmetic is usually pushed to the storage engine and is fast. It becomes expensive when the expression references measures or uses complex logic, because each row then needs the formula engine — and each measure reference triggers a context transition. Iterate the smallest table that gives the right granularity (a dimension rather than the fact table) whenever you can.

Frequently asked questions

Is SUMX slower than SUM?
Not for simple column arithmetic, which the storage engine handles. SUMX becomes slower when its expression references measures or branches, forcing row-by-row work in the formula engine.
Why does my SUMX total differ from the sum of the rows?
It usually does not — that is SUMX's advantage over a ratio measure. If totals look wrong, the expression probably depends on a non-additive measure; iterate the column that defines the visual's rows to make the total consistent.
Can I use SUMX in a calculated column?
Yes, but think about what table it iterates. In a calculated column, use RELATEDTABLE to iterate the related rows of the current row.

Related DAX concepts