Downloads
DAXAggregationIntermediate

SUMX

Evaluates an expression on each row of a table, then adds the results.

Syntax
SUMX(<table>, <expression>)

Returns: A number (a scalar). BLANK when the expression has no values to add.

What it does

SUMX walks down a table one row at a time, works out the expression on that row, and adds up all the results. It is the tool for totals that need a calculation on each line, such as quantity times unit cost. The filter context still applies, so only the rows that survive the slicers and filters are visited.

Syntax and arguments

Example

The operations team wants standard cost, which is the quantity sold times the unit cost held on the product list, not the cost recorded on each sale.

Standard Cost =
SUMX(
    Sales,
    Sales[SalesQuantity] * RELATED(Product[UnitCost])
)

For each sales line, RELATED fetches the unit cost of that product. SUMX multiplies it by the quantity and adds the lines together. Compare this with the recorded cost in Sales[TotalCost] to see whether cost is booked at the standard rate.

Example: a second, different use

Profit earned only on lines that made money, for a review of the profitable part of the business:

Profitable Line Margin =
SUMX(
    FILTER(Sales, Sales[SalesAmount] > Sales[TotalCost]),
    Sales[SalesAmount] - Sales[TotalCost]
)

FILTER keeps only the lines with a positive margin, then SUMX adds the margin of those lines. Loss lines are left out entirely, not netted off.

Things to watch

In business terms

SUMX is how finance builds extended amounts: quantity times price for invoice totals, hours times rate for labor cost, units times standard cost for inventory valuation, and any margin calculated line by line before it is added up.

Related functions

All DAX functions · View as Markdown · DAX fundamentals