Menu
×
   ❮   
HTML CSS JAVASCRIPT SQL PYTHON JAVA PHP C C++ C# AWS W3.CSS HOW TO BOOTSTRAP REACT MYSQL JQUERY EXCEL XML DJANGO NUMPY PANDAS NODEJS DSA TYPESCRIPT ANGULAR ANGULARJS GIT POSTGRESQL MONGODB ASP AI R GO KOTLIN SWIFT SASS VUE GEN AI SCIPY CYBERSECURITY DATA SCIENCE INTRO TO PROGRAMMING HTML & CSS BASH RUST TOOLS MATH

Power BI DAX SUMX and Iterators


Share

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)
ArgumentMeaning
tableThe table to go through, like Sales.
expressionWhat 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:

CategoryTotal SalesTotal Sales SUMX
Balls4,447,2004,447,200
Medicine4,413,6004,413,600
Status Healers556,000556,000
Total9,416,8009,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:

OrderIDProductIDQuantityRELATED UnitPriceQuantity * UnitPrice
1001P0611,2001,200
1002P01192003,800
1003P02136007,800
1004P089100900
1005P04193005,700
...............
Sum of all 2,740 rows9,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])))
CategoryProfitProfit SUMX
Balls1,969,6401,969,640
Medicine2,027,0602,027,060
Status Healers295,495295,495
Total4,292,1954,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.)

RegionTotal SalesAvg Sales per Store
Hoenn2,972,0001,486,000
Johto3,363,6501,681,825
Kanto3,081,1501,540,575
Total9,416,8001,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

FunctionReturnsExampleResult
SUMXThe sum of the row resultsSUMX(Sales, Sales[Quantity] * RELATED(Products[UnitPrice]))9,416,800
AVERAGEXThe average of the row resultsAVERAGEX(Stores, [Total Sales])1,569,467
MINXThe smallest row resultMINX(Sales, Sales[Quantity] * RELATED(Products[UnitPrice]))200
MAXXThe largest row resultMAXX(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:

  1. A calculated column Amount, and the measure SUM(Sales[Amount])
  2. One measure with SUMX, and no extra column
Calculated column + SUMMeasure with SUMX
CalculatedWhen the data is loaded or refreshedEach time a visual needs it
Stored in the modelYes, one value for every rowNo
Can go in Rows, slicers and filtersYesNo
Model sizeBigger, because the column is storedNo 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.


Free account

Track your progress

XP earned 0 Day streak 0 Spaces 0 League —

New

Learn in Adventure Mode W3Schools Adventure App

Coding fundamentals as bite-sized lessons and challenges.

×

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
sales@w3schools.com

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail:
help@w3schools.com

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookies and privacy policy.

Copyright 1999-2026 by Refsnes Data. All Rights Reserved. W3Schools is Powered by W3.CSS.