Standard aggregation functions like SUM can only operate on a single column — the moment your calculation involves multiplying or combining columns before aggregating, you need iterator functions. This lesson breaks down exactly how SUMX, AVERAGEX, and MAXX work, why row context is the key to understanding them, and when to use each one.
Imagine you're building a sales report and your manager asks for total revenue. Easy enough — you multiply quantity by unit price and sum it up. But when you try to do this in DAX using SUM, you immediately hit a wall: SUM can only add up a single column. It has no idea how to multiply two columns together before adding them. This is the exact moment when most Power BI beginners get stuck, and it's the exact moment when iterator functions save the day.
Iterator functions — commonly called "X functions" because their names end in X — are one of the most powerful and frequently misunderstood concepts in DAX. They don't just aggregate a column. They walk through a table row by row, evaluate an expression for each row, and then aggregate those results. That might sound simple, but it unlocks a whole category of calculations that would otherwise be impossible with standard aggregation functions like SUM, AVERAGE, or MAX.
By the end of this lesson, you'll understand exactly how iterator functions work under the hood, when to use them instead of their simpler counterparts, and how to write real-world measures using SUMX, AVERAGEX, and MAXX. You'll also understand a critical concept called row context, which is the engine that makes iterators tick.
What you'll learn:
This lesson assumes you're comfortable navigating Power BI Desktop and understand the basic idea of writing a DAX measure. You should know what a measure is and have at least seen a formula like Total Sales = SUM(Sales[Amount]) before. If you want to deepen your understanding of how DAX evaluates context around measures, the lesson on row context vs filter context is an excellent companion read. A passing familiarity with calculated columns vs measures will also help you follow along.
Let's start with a realistic dataset. Suppose you have a sales table called Orders with the following columns:
| OrderID | Product | Quantity | UnitPrice | DiscountPct |
|---|---|---|---|---|
| 1001 | Widget A | 5 | 20.00 | 0.10 |
| 1002 | Widget B | 3 | 50.00 | 0.00 |
| 1003 | Widget A | 8 | 20.00 | 0.15 |
| 1004 | Widget C | 2 | 120.00 | 0.05 |
Your goal is to calculate total revenue after discount. The formula for a single row is:
Revenue = Quantity × UnitPrice × (1 - DiscountPct)
For row 1001, that's 5 × 20.00 × 0.90 = 90.00. For row 1002, it's 3 × 50.00 × 1.00 = 150.00. And so on.
Now here's where SUM breaks down. You might think to write:
-- This does NOT work as intended
Total Revenue = SUM(Orders[Quantity]) * SUM(Orders[UnitPrice]) * (1 - SUM(Orders[DiscountPct]))
This takes the total of all quantities (18), multiplies by the total of all unit prices (210), and multiplies by a nonsensical discount factor. The result is wildly wrong — it's treating totals as if they represent a single transaction.
What you actually need is to evaluate the revenue formula for each row individually, and then sum those individual results. That's precisely what SUMX does.
Key insight: SUM collapses an entire column into a single number immediately. Iterators like SUMX evaluate an expression at the row level first, then collapse the results. The order of operations is fundamentally different — and it matters enormously.
Before you write a single X function, you need to understand row context. This is the concept that makes iterators work, and getting it wrong is the root cause of most iterator-related bugs.
Row context is exactly what it sounds like: when DAX is processing a specific row in a table, it has "context" about that row. It knows the value of every column for that particular row. Inside an iterator, DAX establishes a row context for each row in the table it's looping over, which means your expression can refer to column values as if it's sitting inside that single row.
Think of it like a payroll officer going through a stack of timesheets one at a time. For each timesheet, they look at the employee's hourly rate and hours worked, calculate the pay, write it on a sticky note, and move to the next sheet. When they're done, they add up all the sticky notes. The "current timesheet" they're looking at is the row context.
This is different from filter context, which is about which rows are visible at all (determined by slicers, filters, and visual interactions). If you want to go deeper on how these two contexts interact, the article on understanding DAX CALCULATE and filter context covers this thoroughly.
Note: Row context exists automatically inside iterators, but it does NOT exist inside regular measures. This is why you can't reference
Orders[Quantity]by itself in a measure and expect it to mean "the quantity of the current row" — there is no current row in a plain measure. There is one inside an iterator.
SUMX is the most commonly used iterator, and it's the right tool for the revenue problem we described above.
Syntax:
SUMX(<table>, <expression>)
<table> — the table to iterate over, row by row<expression> — the formula to evaluate for each row; the results get summed upHere's the correct revenue measure:
Total Revenue =
SUMX(
Orders,
Orders[Quantity] * Orders[UnitPrice] * (1 - Orders[DiscountPct])
)
Let's trace through what DAX does here:
5 × 20 × 0.90 = 90.3 × 50 × 1.00 = 150.8 × 20 × 0.85 = 136.2 × 120 × 0.95 = 228.90 + 150 + 136 + 228 = 604.That's the correct answer, and SUM alone could never get there.
Tip: You'll often see SUMX used with a FILTER as the first argument — for example,
SUMX(FILTER(Orders, Orders[Region] = "North"), ...). This is valid and powerful, but for most cases, let filter context (from slicers and visuals) do the filtering for you. Embedding FILTER inside SUMX can hurt performance if overused. See the lesson on DAX aggregation functions demystified for more on when each approach fits.
SUMX isn't always better than SUM. If you simply want to add up a single, pre-computed column — like a TotalPrice column that already exists in your table — then SUM(Orders[TotalPrice]) is faster and cleaner. Use SUM when the column already holds the value you want to aggregate. Use SUMX when you need to compute something per row before aggregating.
AVERAGEX works the same way as SUMX, but instead of summing the row-level results, it averages them. This distinction matters more than it might first appear.
Syntax:
AVERAGEX(<table>, <expression>)
The scenario: You want to know the average revenue per order — not the average of any single column, but the average of the calculated revenue per order.
Avg Revenue Per Order =
AVERAGEX(
Orders,
Orders[Quantity] * Orders[UnitPrice] * (1 - Orders[DiscountPct])
)
Using our dataset, AVERAGEX computes [90, 150, 136, 228] and then averages them: (90 + 150 + 136 + 228) / 4 = 151.
Now here's a subtle trap that catches beginners. What if you tried to calculate average margin percentage? Suppose you add a cost column and define margin as (Revenue - Cost) / Revenue. You might try:
-- Naive approach: potentially wrong
Avg Margin % = AVERAGE(Orders[MarginPct])
If MarginPct is a stored column, AVERAGE will weight every row equally regardless of how large that order was. A tiny 5-unit order with a 40% margin would pull the average just as much as a 500-unit order. AVERAGEX lets you make this a revenue-weighted calculation if that's what the business logic demands. Whether equal-weighting or revenue-weighting is "correct" is a business decision — but AVERAGEX gives you the control to make it either way.
Warning: AVERAGEX divides by the number of rows in the table it iterates over — not by a sum or some other denominator you might expect. Always double-check that the denominator matches your business definition of "average." If you want a weighted average, you'll need a different pattern:
SUMX(table, value * weight) / SUMX(table, weight).
MAXX follows the same iterator pattern but returns the single highest value from all the row-level results.
Syntax:
MAXX(<table>, <expression>)
The scenario: You want to find the single highest revenue order in the current filter context.
Highest Order Revenue =
MAXX(
Orders,
Orders[Quantity] * Orders[UnitPrice] * (1 - Orders[DiscountPct])
)
MAXX evaluates the revenue for each row — [90, 150, 136, 228] — and returns the maximum: 228.
This is the kind of measure that becomes genuinely useful inside a report. Imagine a card visual showing "Best Single Order Value" that updates dynamically when you filter by region or product category. MAXX makes this trivial.
MINX works identically but returns the minimum, and COUNTX counts rows where the expression returns a non-blank result. The whole X family shares the same two-argument iterator pattern.
Tip: MAXX is especially useful in combination with date logic. For example,
MAXX(FILTER(Orders, Orders[OrderDate] = MAX(Orders[OrderDate])), Orders[Quantity] * Orders[UnitPrice])gives you the highest revenue order on the most recent day. As you get more comfortable with iterators, you'll find them showing up inside other expressions frequently. The lesson on advanced DAX patterns with variables, SWITCH, and iterator functions covers creative combinations like these.
Real-world measures often need to combine iterator logic with other calculations. This is where things can get complex fast, and where variables become your best friend.
Suppose you want to calculate how much revenue comes from orders that are above the average order size (by quantity). Here's one approach:
Revenue From Above-Avg Orders =
VAR AvgQty = AVERAGE(Orders[Quantity])
RETURN
SUMX(
FILTER(Orders, Orders[Quantity] > AvgQty),
Orders[Quantity] * Orders[UnitPrice] * (1 - Orders[DiscountPct])
)
Breaking this down:
VAR AvgQty captures the average quantity before the iterator starts, locking it in as a scalar valueFILTER(Orders, Orders[Quantity] > AvgQty) creates a reduced table containing only high-quantity ordersSUMX then iterates over just those rows and computes revenueUsing a variable here is critical. If you tried to put AVERAGE(Orders[Quantity]) directly inside the FILTER expression, you'd risk unexpected behavior because DAX would re-evaluate it at each row. Variables prevent that — they evaluate once and store the result. For a deeper look at this pattern, see the guide on DAX variables in practice.
Here's something that trips up a lot of intermediate DAX writers: iterators don't ignore filter context. They operate within the current filter context.
If your report has a slicer set to "Region = West," then SUMX(Orders, ...) only iterates over the orders from the West region. The filter context that Power BI has established for that visual narrows down which rows the iterator sees. This is usually exactly what you want — it's what makes your measure respond correctly to user interactions.
But there are times when you want the iterator to see all rows regardless of context. That's when you'd combine SUMX with ALL:
Total Revenue All Regions =
SUMX(
ALL(Orders),
Orders[Quantity] * Orders[UnitPrice] * (1 - Orders[DiscountPct])
)
The ALL(Orders) overrides any active filters and hands SUMX the full table to iterate over. This is a niche use case, but understanding it shows you how iterator functions sit inside the broader DAX context system. The article on DAX table functions: FILTER, ALL, ALLEXCEPT, and VALUES explains these table-modifying functions in detail.
Key insight: The table argument in an X function is always evaluated in the current filter context first. Then the iterator walks through whatever rows remain. This two-step evaluation is subtle but important — it's why your measures respond dynamically to slicers even when they contain SUMX.
Open Power BI Desktop and build the following model from scratch. You can enter data manually by going to Home tab → Enter Data, and typing in the values below.
Table name: Orders
| OrderID | Product | Region | Quantity | UnitPrice | CostPerUnit |
|---|---|---|---|---|---|
| 1001 | Widget A | East | 5 | 20.00 | 12.00 |
| 1002 | Widget B | West | 3 | 50.00 | 35.00 |
| 1003 | Widget A | East | 8 | 20.00 | 12.00 |
| 1004 | Widget C | West | 2 | 120.00 | 80.00 |
| 1005 | Widget B | East | 6 | 50.00 | 35.00 |
Once your table is loaded, go to the Report view and create the following measures by clicking New Measure in the Modeling tab:
Measure 1 — Total Revenue:
Total Revenue =
SUMX(
Orders,
Orders[Quantity] * Orders[UnitPrice]
)
Measure 2 — Total Profit:
Total Profit =
SUMX(
Orders,
Orders[Quantity] * (Orders[UnitPrice] - Orders[CostPerUnit])
)
Measure 3 — Average Profit Per Order:
Avg Profit Per Order =
AVERAGEX(
Orders,
Orders[Quantity] * (Orders[UnitPrice] - Orders[CostPerUnit])
)
Measure 4 — Best Single Order Profit:
Best Order Profit =
MAXX(
Orders,
Orders[Quantity] * (Orders[UnitPrice] - Orders[CostPerUnit])
)
Now add a table visual to your report canvas. Drag the Region column and all four measures into the visual. Then add a slicer based on Product.
Toggle the slicer between products and observe how every measure updates. Notice that SUMX is only iterating over the filtered rows — orders for the selected product — not the entire table. This is filter context and iterator functions working together in real time.
Try adding a card visual for Best Order Profit and watch it respond to your product slicer. That's MAXX dynamically finding the highest-profit order within whatever filter context is active.
Mistake 1: Using SUM of a product instead of SUMX
-- Wrong
Wrong Revenue = SUM(Orders[Quantity]) * SUM(Orders[UnitPrice])
-- Right
Total Revenue = SUMX(Orders, Orders[Quantity] * Orders[UnitPrice])
The wrong version multiplies totals together, not row-level values. If your products have different prices, the result will be completely wrong.
Mistake 2: Trying to reference a column in a measure without an iterator
-- Wrong: Orders[Quantity] has no row context here
Bad Measure = Orders[Quantity] * Orders[UnitPrice]
This will either error or return unexpected results because there's no row context in a standalone measure. If you need row-by-row evaluation, you need an iterator.
Mistake 3: Confusing AVERAGEX's denominator
AVERAGEX divides by the count of rows it iterates over. If some rows produce a blank result from your expression (because of division by zero or missing data), AVERAGEX may still count those rows in the denominator depending on the situation. Use IFERROR and DIVIDE for error handling inside your expression to make this predictable.
Mistake 4: Iterating over huge tables unnecessarily
SUMX over a 10-million-row fact table with a complex expression inside can be slow. Where possible, pre-compute columns in Power Query (not in DAX) to reduce what the iterator needs to calculate at query time. The lesson on performance tuning DAX with DAX Studio teaches you how to diagnose and fix these slow measures.
Warning: Using SUMX where a simple SUM would do is a common beginner habit that can hurt report performance at scale. If the column you want to sum already exists in your table and doesn't need any row-level transformation, always prefer
SUM(Table[Column])overSUMX(Table, Table[Column]). They produce the same result, but SUM is significantly faster because it doesn't establish row context.
Mistake 5: Forgetting that the table argument responds to filter context
If you put SUMX inside a measure and expect it to always see the full table, you'll be surprised when a slicer cuts the result. This behavior is usually correct — it's what makes measures dynamic. But if you intentionally need all rows, pass ALL(Table) as the first argument.
You've now seen how iterator functions work from the inside out. The core idea is straightforward: instead of aggregating a single column directly, an X function walks through a table row by row, evaluates your expression in the context of each row, and then aggregates all those results. That loop is called iteration, and the per-row awareness that makes it possible is called row context.
SUMX sums the row-level results. AVERAGEX averages them. MAXX finds the highest. And the same pattern extends to MINX, COUNTX, RANKX, and more. Once you have the mental model of "iterate, then aggregate," every X function becomes readable.
The most important practical takeaway: reach for an X function whenever your calculation requires multiplying, dividing, or combining two or more columns before aggregating. That's the signal. A single column being added up? Use SUM. A formula that needs to be evaluated per row first? Use SUMX.
From here, there are a few natural next steps to deepen your DAX skills:
Master the iterator pattern, and you'll find that a huge swath of DAX problems that seemed difficult suddenly have clean, readable solutions.