"Iterators: SUMX, AVERAGEX and friends"

01The problem

The sales director asks a simple-sounding question: "What's our average sale?" Three analysts give three answers:

• ₦194,689: the average order line.
• ₦6.66m: the average customer in 2025.
• ₦44.98m: the average month in 2025.

All three are correct. They're averages of different things. An "average" in DAX is never just AVERAGE(column): you have to decide what you're averaging over, and that's what iterators let you say.

02The concept

An iterator loops over a table, evaluates an expression for each row (in row context), then aggregates the results:

SUMX ( <table>, <expression> )

SUM ( orders[quantity] ) is really shorthand for SUMX ( orders, orders[quantity] ).

The table you iterate decides the "average of what"

VALUES ( column ) returns the distinct values of a column that are visible in the current filter context, so it's the natural thing to iterate when you want "per customer" or "per month".

When you iterate a list of customers and call [Revenue], context transition filters each customer in turn, which is exactly what lesson 2 explained. And because AVERAGEX skips blanks, months with no sales (such as July 2026, which hasn't happened) don't drag the average down.

RELATED inside an iterator

While iterating orders (the many side), RELATED ( products[list_price] ) fetches the matching value from the one side. That lets you compare what customers paid with the list price.

03Example

Avg Line Value = AVERAGEX ( orders, orders[quantity] * orders[unit_price] * ( 1 - orders[discount_pct] / 100 ) )

Avg Revenue per Customer = AVERAGEX ( VALUES ( orders[customer_id] ), [Revenue] )

Avg Monthly Revenue = AVERAGEX ( VALUES ( 'Date'[Year Month] ), [Revenue] )

Best Month Revenue = MAXX ( VALUES ( 'Date'[Year Month] ), [Revenue] )

Revenue at List Price = SUMX ( orders, orders[quantity] * RELATED ( products[list_price] ) )

Below List % = 1 - DIVIDE ( [Revenue], [Revenue at List Price] )

Best Month Revenue shows ₦66.3m across all dates: December 2025, the festive peak. In a matrix by year, it shows each year's best month instead, because VALUES ( 'Date'[Year Month] ) only returns the months in that year's filter context.

04Walkthrough

1. Add the six measures to _Measures and format them.
2. Build a table with Date[Year] and Avg Line Value, Avg Revenue per Customer and Avg Monthly Revenue. The three "averages" differ by factors of about 30 and 200.
3. Replace VALUES ( orders[customer_id] ) with VALUES ( customers[customer_id] ) and compare the 2025 figure. It's the same, because [Revenue] is blank for the nine customers who didn't buy in 2025 and AVERAGEX skips them. Now try [Revenue] + 0 inside the AVERAGEX: the zeros count, and the average falls. Decide which you mean, and write it deliberately.
4. Put Below List % in a table by Date[Year]. In 2026 it's just the discount; in 2025 it's much bigger. The difference is the January 2026 price rise: 2025 sales were at the old prices.

05Practice

06Challenge

07More practice

08Check your understanding