DAX SUMX Function

SUMX is an iterator function in DAX that:

• Iterates over a table
• Evaluates an expression for each row
• Returns the sum of those evaluated values

Syntax

SUMX(<table>, <expression>)

• <table>: The table (real or virtual) to iterate over
• <expression>: An expression to evaluate for each row in the table

Concept

Think of SUMX as a loop:

• For each row in the table:

  • Evaluate the expression (e.g., Price * Quantity)
  • Sum all the results

This is different from SUM, which just adds up values in a single column no row-by-row calculation.

Example

Let's say you have a Sales table:

You want to calculate total revenue (Quantity * UnitPrice for each row).

• This won't work:

SUM(Sales[Quantity] * Sales[UnitPrice]) 
-- INVALID! SUM can't do row-by-row multiplication

Use SUMX

SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])

Result

Row 1: 2 × 10 = 20 Row 2: 3 × 15 = 45 Row 3: 1 × 20 = 20 Total: 20 + 45 + 20 = 85

When to Use SUMX (vs SUM)

Real-World Example (with FILTER)

Calculate total sales for products with quantity > 2:

SUMX(
    FILTER(Sales, Sales[Quantity] > 2),
    Sales[Quantity] * Sales[UnitPrice]
)

Here, SUMX is summing revenue only for rows where Quantity > 2, thanks to FILTER.

Summary