Power BI DAX SUMX and Iterators
What Is an Iterator?
SUM adds up the values of one column, like SUM(Sales[Amount]).
But what if the number you want to add up is not stored in a column?
An iterator function goes through a table one row at a time.
For each row, it calculates an expression.
At the end, it combines the results.
The names of most iterator functions end with an X: SUMX, AVERAGEX, MINX, MAXX, and more.
This page uses the Poke Mart model described in DAX Introduction, with the measures Total Sales from DAX Measures, and Profit and Avg Order Value from DAX DIVIDE.
Syntax
SUMX(table, expression)
| Argument | Meaning |
|---|---|
| table | The table to go through, like Sales. |
| expression | What to calculate for each row, like Sales[Quantity] * RELATED(Products[UnitPrice]). |
Total Sales Without the Amount Column
The Total Sales measure, SUM(Sales[Amount]), needs the Amount calculated column.
With SUMX, you can calculate the same total straight from the quantity and the price in the Products table:
Example
Total Sales SUMX = SUMX(Sales, Sales[Quantity] * RELATED(Products[UnitPrice]))
Put both measures in a matrix by Category:
| Category | Total Sales | Total Sales SUMX |
|---|---|---|
| Balls | 4,447,200 | 4,447,200 |
| Medicine | 4,413,600 | 4,413,600 |
| Status Healers | 556,000 | 556,000 |
| Total | 9,416,800 | 9,416,800 |
The two measures give the same results in every row.
The SUMX version does not use the Amount column at all.
How SUMX Works
SUMX goes through the Sales table and creates a row context for each row, just like a calculated column does:
| OrderID | ProductID | Quantity | RELATED UnitPrice | Quantity * UnitPrice |
|---|---|---|---|---|
| 1001 | P06 | 1 | 1,200 | 1,200 |
| 1002 | P01 | 19 | 200 | 3,800 |
| 1003 | P02 | 13 | 600 | 7,800 |
| 1004 | P08 | 9 | 100 | 900 |
| 1005 | P04 | 19 | 300 | 5,700 |
| ... | ... | ... | ... | ... |
| Sum of all 2,740 rows | 9,416,800 | |||
For each row, RELATED follows the relationship to the Products table and returns the UnitPrice of that row's product.
RELATED needs a row context, and SUMX gives it one.
That is why RELATED works inside SUMX, but not on its own in a measure.
The row results are not stored anywhere.
SUMX calculates them when a visual needs them, in the current filter context.
In the Balls row of the matrix, SUMX only goes through the 1,035 Ball orders.
Profit in One Measure
The expression can be as long as you need.
This measure calculates the profit of each order from the price and the cost, without the Amount and Cost columns:
Example
Profit SUMX = SUMX(Sales, Sales[Quantity] * (RELATED(Products[UnitPrice]) - RELATED(Products[UnitCost])))
| Category | Profit | Profit SUMX |
|---|---|---|
| Balls | 1,969,640 | 1,969,640 |
| Medicine | 2,027,060 | 2,027,060 |
| Status Healers | 295,495 | 295,495 |
| Total | 4,292,195 | 4,292,195 |
AVERAGEX
AVERAGEX works like SUMX, but it returns the average of the row results instead of the sum.
Syntax
AVERAGEX(table, expression)
Example
Avg Order Amount = AVERAGEX(Sales, Sales[Quantity] * RELATED(Products[UnitPrice]))
Formatted as Decimal number with 2 decimal places, it shows 3,436.79 in the Total row of a table visual: the average amount of the 2,740 orders.
That is the same as the Avg Order Value measure, which divides Total Sales by Order Count.
Iterate Over Another Table
The table in an iterator does not have to be Sales.
This measure goes through the six rows of the Stores table:
Example
Avg Sales per Store = AVERAGEX(Stores, [Total Sales])
For each store, AVERAGEX calculates Total Sales for that store only.
Then it takes the average of the results.
(When you use a measure inside an iterator, Power BI calculates it for the current row, here the current store.)
| Region | Total Sales | Avg Sales per Store |
|---|---|---|
| Hoenn | 2,972,000 | 1,486,000 |
| Johto | 3,363,650 | 1,681,825 |
| Kanto | 3,081,150 | 1,540,575 |
| Total | 9,416,800 | 1,569,467 |
Each region has two stores, so its average is half of its total.
The Total row averages all six stores: 9,416,800 / 6 = 1,569,466.67, shown as a whole number.
Other Iterators
| Function | Returns | Example | Result |
|---|---|---|---|
| SUMX | The sum of the row results | SUMX(Sales, Sales[Quantity] * RELATED(Products[UnitPrice])) | 9,416,800 |
| AVERAGEX | The average of the row results | AVERAGEX(Stores, [Total Sales]) | 1,569,467 |
| MINX | The smallest row result | MINX(Sales, Sales[Quantity] * RELATED(Products[UnitPrice])) | 200 |
| MAXX | The largest row result | MAXX(Sales, Sales[Quantity] * RELATED(Products[UnitPrice])) | 9,600 |
The smallest order is worth 200, and the largest is worth 9,600 (19 different orders reach it, each of 8 Ultra Balls).
These are the same results as Smallest Order and Largest Order on the Aggregations page, but MINX and MAXX do not need the Amount column.
Iterator or Calculated Column?
You can now get Total Sales in two ways:
- A calculated column Amount, and the measure
SUM(Sales[Amount]) - One measure with SUMX, and no extra column
| Calculated column + SUM | Measure with SUMX | |
|---|---|---|
| Calculated | When the data is loaded or refreshed | Each time a visual needs it |
| Stored in the model | Yes, one value for every row | No |
| Can go in Rows, slicers and filters | Yes | No |
| Model size | Bigger, because the column is stored | No extra size |
Rules of thumb:
- Use a calculated column when you need the value of each row in a visual: to see it in a table, to sort or group by it, or to use it in a slicer.
- Use an iterator in a measure when you only need the result, like a total or an average.
In the small Poke Mart model, both ways work well.
In a model with millions of rows, every stored column makes the file bigger, so avoid columns that you only need for a total.
What You Learned
- An iterator goes through a table row by row, calculates an expression for each row, and combines the results.
SUMX(Sales, Sales[Quantity] * RELATED(Products[UnitPrice]))gives the same 9,416,800 as Total Sales, without the Amount column.- Iterators create a row context, so RELATED works inside them.
- AVERAGEX, MINX and MAXX work the same way, and the table can be any table, like Stores.
- Use calculated columns for values you need per row in visuals, and iterators for results.