Overview
SUM operates at the column level—it adds one column under active filters and cannot do Quantity × Price inside its brackets. SUMX is an iterator: it loops row-by-row, runs an expression per row, then sums the results.
Syntax
SUM
SUM(<column>)SUMX
SUMX(<table>, <expression>)- SUM(<column>): Requires a single column reference only.
- SUMX(<table>, <expression>): Table name first, then the row-by-row math equation.
Example & Dataset
A billing table named SalesTransactions:
Sample Data Table: SalesTransactions
| TransactionID | Product | Quantity | UnitPrice |
|---|---|---|---|
| T_01 | Desk | 2 | 200 |
| T_02 | Chair | 5 | 50 |
| T_03 | Lamp | 10 | 20 |
The DAX Code
You cannot use SUM(SalesTransactions[Quantity] * SalesTransactions[UnitPrice])—SUM will throw a syntax error. Use SUMX (recommended):
Approach 1: SUMX (Recommended)
Total Revenue (SUMX) =
SUMX(
SalesTransactions,
SalesTransactions[Quantity] * SalesTransactions[UnitPrice]
)Approach 2: SUM on a Calculated Column (Avoid)
// Requires a physical [LineTotal] column first
Total Revenue (SUM) = SUM(SalesTransactions[LineTotal])Note
Avoid Approach 2—physical columns increase Power BI file size and waste RAM.
Expected Output Visual
Output Table Visual
| Product | Quantity (SUM) | Unit Price (Avg) | Total Revenue (SUMX) |
|---|---|---|---|
| Desk | 2 | 200 | 400 (2 × 200) |
| Chair | 5 | 50 | 250 (5 × 50) |
| Lamp | 10 | 20 | 200 (10 × 20) |
| Total | 17 | 90 | 850 |
Why Did This Output Happen?
SUMX calculated 2×200=400, 5×50=250, and 10×20=200, then summed to $850—without creating extra physical columns.