Most DAX aggregation functions — SUM, AVERAGE, COUNT — operate on a single column. They take every value in that column and aggregate it. SUMX and AVERAGEX work differently. They iterate row by row through a table, evaluate an expression for each row, and then aggregate those results. This distinction matters enormously when the expression involves a calculation — such as multiplying two columns together — rather than simply reading one column.
This guide explains the X-function pattern, why it produces different results than simple column aggregation, and six practical examples. Topics include revenue from quantity times price, margin per customer, weighted average price, row-level conditional aggregation, iterating a related table, and a filtered SUMX for conditional revenue.
Why SUMX Exists — The Row-by-Row Difference
Consider a sales table with Quantity and Price columns. You want total revenue. SUM(Sales[Quantity]) * SUM(Sales[Price]) multiplies the total quantity by the total price — but that is mathematically wrong if different products have different prices. Instead, SUMX evaluates Quantity * Price for each individual row, then sums those row-level results. This is the correct approach.
| Approach | Formula | What it calculates | Correct for |
|---|---|---|---|
| Wrong approach | SUM(Quantity) * SUM(Price) | Total qty times total price (incorrect for mixed prices) | Only when all rows have identical price |
| SUMX (correct) | SUMX(Sales, [Quantity] * [Price]) | Sum of (qty * price) per row | Any multi-price dataset |
SUMX and AVERAGEX Syntax
Examples 1–4: SUMX and AVERAGEX in Practice
This is the canonical SUMX example. Each row has a Quantity and a Price. Revenue per row is Quantity * Price. SUMX sums these row-level products to produce the correct total revenue. A plain SUM approach would produce an incorrect result whenever rows have different prices.
Gross margin is revenue minus cost. Calculating margin correctly requires the subtraction at the row level before summing. SUMX ensures this row-by-row approach. Specifically, it computes (Revenue − Cost) for each row, then sums those margins to produce the total gross margin.
A simple AVERAGE of a Price column gives each row equal weight. AVERAGEX can apply a weighted average instead by defining a custom expression per row. For example, averaging the unit price weighted by order size gives a revenue-weighted average selling price — a more meaningful metric for pricing analysis.
SUMX can iterate a filtered subset of rows rather than the full table. Pass a FILTER expression as the table argument. This is DAX’s equivalent of SUMIF. It is more flexible than CALCULATE+SUM because the filter condition can reference row-level calculated expressions, not just stored columns.
Examples 5–6: Related Table Iteration and Average per Category
SUMX can iterate any table — including a related table retrieved via RELATEDTABLE. This lets you sum a fact table value from the perspective of a dimension table row. For example, summing all orders linked to each customer, starting from the Customers table rather than the Orders table.
AVERAGEX over a raw table averages individual transaction rows. Sometimes you need the average of category-level totals instead — for example, the average revenue per region. This requires first creating a summary table with one row per category, then averaging the totals. AVERAGEX with SUMMARIZE achieves this in a single measure.
Common Issues and How to Fix Them
SUMX returns a different result than expected
The most common cause is column reference scope. Inside SUMX, column references use the current row being iterated. If your expression accidentally references a measure that aggregates the entire table rather than the current row, the result will be wrong. Verify that the expression uses direct column references (Sales[Price]) rather than aggregation measures ([Avg Price]) inside the row expression. Specifically, use [column] syntax for row values and reserve measure references for post-aggregation calculations.
AVERAGEX ignores blank rows in the expression
AVERAGEX skips rows where the expression evaluates to blank. This is consistent with DAX averaging behaviour. However, if you expect blank rows to contribute a zero to the average, wrap the expression in IFERROR or use IF(ISBLANK(...), 0, expression) to convert blanks to zeros before averaging. This ensures blank rows are counted in the denominator.
Frequently Asked Questions
-
What is the difference between SUM and SUMX?+SUM adds up all values in a single column. SUMX iterates every row in a table, evaluates an expression for each row, and then sums those results. Use SUM when the value you need is already in a single column. Use SUMX when the value requires a per-row calculation first, such as Quantity times Price. SUMX is more powerful but also slower on large tables, so avoid it when SUM alone is sufficient.
-
When should I use AVERAGEX instead of AVERAGE?+Use AVERAGE when you want the mean of values in a single column. Use AVERAGEX when the value to average must be calculated per row. For instance, averaging the profit margin requires dividing (Price minus Cost) by Price on each row before averaging — AVERAGEX handles this correctly. A plain AVERAGE cannot compute per-row expressions. Also use AVERAGEX when you need to average totals per category rather than individual row values, by passing a SUMMARIZE table as the first argument.
-
Can SUMX and AVERAGEX use a FILTER as the table?+Yes. The first argument of SUMX and AVERAGEX can be any table expression, including FILTER results. SUMX(FILTER(Sales, Sales[Region] = "North"), Sales[Revenue]) sums revenue for North only. This is equivalent to CALCULATE(SUM(Sales[Revenue]), Sales[Region] = "North") in most cases, but SUMX with FILTER is more flexible when the filter condition involves a row-level calculated expression that cannot be expressed as a simple column filter.
-
Why is SUMX slower than SUM?+SUM uses a highly optimised column-store scan in the DAX storage engine. It does not evaluate expressions — it simply totals a pre-built column. SUMX, on the other hand, iterates row by row and evaluates an expression for each row. This row-by-row evaluation is handled by the DAX formula engine, which is significantly slower than the storage engine. On tables with millions of rows, SUMX can be noticeably slower than SUM. Always prefer SUM on a pre-calculated column when performance matters.