Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 56 ยท DAX

Aggregators and Iterators: SUM, SUMX, AVERAGEX

Understand row context, the difference between SUM and SUMX, AVERAGEX, DIVIDE, COUNTROWS and DISTINCTCOUNT in DAX, with a pandas stand-in you can run.

Almost every measure you write starts with a simple question: how do I add up, count or average these rows? DAX answers with two families of functions. Aggregators such as SUM and AVERAGE take a single column. Iterators such as SUMX and AVERAGEX walk through a table one row at a time, calculate an expression for each row, and then combine the results.

Choosing correctly between them is the first real DAX skill, and the key to it is row context. This lesson builds on DAX Basics, where you met columns and measures, and prepares the ground for filter context in CALCULATE and Filter Context.

DAX itself cannot run on this site, so the DAX below is read-only. The runnable examples use pandas to reproduce what each formula calculates, and the text says where the stand-in differs from real DAX.

Aggregators: one column in, one number out

The simple aggregators take a column and return a single value over the rows that are currently in scope:

DAX
Total Quantity = SUM(Sales[Quantity])
Average Price  = AVERAGE(Sales[UnitPrice])
Cheapest       = MIN(Sales[UnitPrice])
Priciest       = MAX(Sales[UnitPrice])

Counting has its own family:

Function Counts Notes
COUNT(col) non-blank numbers, dates skips blanks
COUNTA(col) non-blank values of any type
COUNTROWS(table) rows in a table the most common one
DISTINCTCOUNT(col) different values one per unique value
COUNTBLANK(col) blank cells

Use COUNTROWS(Sales) to count the lines of a fact table, and DISTINCTCOUNT(Sales[CustomerKey]) for how many different customers bought something. DISTINCTCOUNT is far more reliable than counting rows when the grain of the table is finer than the thing you count.

Why SUM is not enough

Suppose each sales row has Quantity and UnitPrice. Revenue is the sum of quantity times price per row. This does not work:

DAX
Wrong Revenue = SUM(Sales[Quantity]) * AVERAGE(Sales[UnitPrice])

It multiplies the total quantity by the average price, which only works if every row has the same price. A single aggregator cannot do a calculation row by row. For that, we need an iterator.

Iterators: the X functions

Every iterator has the same two arguments: a table to walk, and an expression to evaluate on each row.

DAX
Revenue = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])
Average Line Value = AVERAGEX(Sales, Sales[Quantity] * Sales[UnitPrice])
Highest Line = MAXX(Sales, Sales[Quantity] * Sales[UnitPrice])

SUMX goes through each row of Sales, calculates quantity times price, and adds up the results. The same pattern exists for AVERAGEX, MINX, MAXX, COUNTX, RANKX and more.

How SUMX works

Take a table Rows in the current filter.
Visit each row Evaluate the expression there.
Combine Add, average, take min or max.

Inside the expression, columns mean this row's values.

1 / 2

The "this row's values" idea is row context: while an iterator visits a row, Sales[Quantity] means the quantity of that row. Calculated columns also have row context, which is why Sales[Quantity] * Sales[UnitPrice] works there without any function around it. Measures have no row context by default, so a bare Sales[Quantity] * Sales[UnitPrice] in a measure gives an error: there is no "current row". Wrapping it in an iterator creates the row context for you.

The table argument can be any table expression

The first argument of an iterator need not be a plain table. It can be a filtered table, or a table of distinct values, which opens up two-step aggregations:

DAX
Revenue Over 100 =
SUMX(
    FILTER(Sales, Sales[Quantity] * Sales[UnitPrice] > 100),
    Sales[Quantity] * Sales[UnitPrice]
)

Average Sales per Customer =
AVERAGEX(VALUES(Customer[CustomerKey]), [Total Sales])

The second one iterates over each customer in the current filter, evaluates [Total Sales] for that customer, and averages the results. Calling a measure inside an iterator triggers a context transition, explained in the next lesson.

DIVIDE: safe division

Dividing by zero is undefined in plain math, and in DAX the / operator returns Infinity or NaN, which look bad in a visual. Use DIVIDE:

DAX
Margin % = DIVIDE([Profit], [Revenue])
Margin % (zero if empty) = DIVIDE([Profit], [Revenue], 0)

DIVIDE(numerator, denominator, [alternate]) returns blank when the denominator is zero or blank, or the alternate result if you pass one. Blank is usually better than 0 for a ratio, because a chart then simply skips the empty point instead of drawing a misleading zero.

Running the logic in pandas

The next example reproduces the three mistakes and fixes, one after the other, on a small table. pandas has no filter context, so we use plain DataFrame operations. The pandas lines show what the DAX formulas calculate.

Python SUM times AVERAGE vs SUMX

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The wrong version gives 363 instead of 210 because the price varies across rows (10, 40, 5, 10, 100). The iterator computed each line total first, then added, which is the only correct order.

Counting functions side by side

Python COUNTROWS, DISTINCTCOUNT and DIVIDE

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Three order IDs spread over five rows is why COUNTROWS and DISTINCTCOUNT differ. If you wanted "orders", counting rows would give 5, overstating the number of orders. The pandas NaN for the blank is a stand-in: DAX has its own BLANK() value, which charts treat differently from zero.

The less common counting and aggregate functions

DAX has a few more members of the family. They are used less often, but you will meet them in other people's models.

Function What it does
COUNTX(table, expr) Evaluates expr for each row and counts the rows where the result is not blank (numbers, dates, booleans). COUNTX(FILTER(Sales, Sales[Amount] > 50), Sales[Amount]) counts only the big sales.
COUNTAX(table, expr) The same, but it also counts text values. COUNTX ignores text.
DISTINCTCOUNTNOBLANK(col) Like DISTINCTCOUNT, but a blank does not count as a value. DISTINCTCOUNT counts the blank as one extra distinct value if the column has any blanks.
AVERAGEA(col) Averages a column that may hold text or booleans: text counts as 0, TRUE as 1 and FALSE as 0. Blanks are skipped.
MAXA(col), MINA(col) The largest and smallest value with the same rule: TRUE is 1, FALSE and text are 0.
PRODUCT(col), PRODUCTX(table, expr) Multiply instead of add. Use PRODUCTX with growth factors to chain percentages.
SUMX, AVERAGEX, MEDIANX Iterator versions of the plain functions, as above.

AVERAGEA and MAXA matter when a column mixes types, which Power BI rarely allows (a column has one type), so you meet them mostly when a column holds TRUE/FALSE flags: AVERAGEA(Customers[IsActive]) is the share of active customers.

DAX
Active Customers  = COUNTX ( Customers, Customers[LastOrderDate] )
Named Customers   = COUNTAX ( Customers, Customers[Name] )
Real Cities       = DISTINCTCOUNTNOBLANK ( Customers[City] )
Share Active      = AVERAGEA ( Customers[IsActive] )
Chained Growth    = PRODUCTX ( Growth, Growth[Factor] ) - 1
Python Stand-in: COUNTX, COUNTAX, DISTINCTCOUNTNOBLANK, AVERAGEA and PRODUCTX

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The blank city is counted by DISTINCTCOUNT (3 values: Oslo, Rome and blank) but not by DISTINCTCOUNTNOBLANK (2). If you ever see one more "city" than you expected, a hidden blank row is usually why.

Choosing quickly

  • A single column, simple total: use the aggregator (SUM, AVERAGE, MIN, MAX).
  • An expression involving two or more columns per row: use the iterator (SUMX, AVERAGEX, ...).
  • Counting lines: COUNTROWS(table). Counting different things: DISTINCTCOUNT(column).
  • Ratios: DIVIDE, never /.
  • Averages of a derived value, such as average per customer: AVERAGEX over the distinct list, not AVERAGE of a column.

Plain aggregators are not slower in any way you would notice on normal data; the engine actually rewrites SUM(col) into SUMX(table, col) internally. Use iterators when you need them, and do not avoid them out of fear. The real performance cost appears when an iterator walks a very large table and calls an expensive measure on every row. That topic returns in Debugging and Optimizing DAX.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Revenue is Quantity times UnitPrice for every row, then added. Which formula is correct?

    SUMX creates a row context, evaluates the expression for each row and adds the results. SUM takes a single column, so the second option is not valid.
  2. 2. What does row context mean?

    Iterators and calculated columns provide a current row. That is row context, as opposed to filter context, which is a set of filters.
  3. 3. A Sales table has one row per order line. Which formula counts the number of different orders?

    COUNTROWS and COUNT count lines, so an order with several lines is counted several times. DISTINCTCOUNT counts each order once.
  4. 4. What does DIVIDE(10, 0) return?

    By default DIVIDE returns blank when the denominator is zero or blank. A third argument lets you choose another value, such as 0.

Summary

  • Aggregators (SUM, AVERAGE, MIN, MAX) take one column; iterators (SUMX, AVERAGEX, MAXX, ...) take a table and an expression evaluated row by row.
  • Row context means "the current row" inside an iterator or calculated column; a bare column in a measure has none.
  • COUNTROWS counts lines, DISTINCTCOUNT counts unique values, and the two often differ.
  • Use DIVIDE for ratios; blank is usually the right result for an undefined ratio.
  • The table argument of an iterator can be filtered or distinct, which enables averages per customer and similar calculations.

Next, the most important function in DAX: CALCULATE and Filter Context.