Downloads

Iterators and table functions

SUMX, RANKX, TOPN, ADDCOLUMNS and when to use each.

Last updated

View as Markdown For AI agents All guides as text

On this page

You'll be able to

  • Tell aggregators and iterators apart, and know when SUMX is needed.
  • Explain why an average of ratios differs from a ratio of sums, and choose the right one.
  • Use RANKX and TOPN for rankings.
  • Build tables with ADDCOLUMNS and count distinct values.

Aggregators versus iterators

SUM(Sales[SalesAmount]) aggregates a column. Its generalization, SUMX(Table, expression), evaluates an expression for every row of a table and then sums the results. Use an X function whenever the value to add up needs row-level arithmetic: SUMX(Sales, Sales[SalesQuantity] * Sales[UnitPrice]). The family: SUMX, AVERAGEX, MINX, MAXX, COUNTX, COUNTAX, PRODUCTX, MEDIANX, PERCENTILEX.INC/EXC, STDEVX.P/S, RANKX, CONCATENATEX, GEOMEANX.

Sales, one row at a time Row Qty Price Qty × Price Running sum 1 10 25 250 250 2 4 120 480 730 3 6 15 90 820 SUMX result: 820 250 + 480 + 90, one row at a time SUM(Qty) gives 20 units, not a money total.
SUMX visits each row, works out a value there, then adds the values. SUM only adds one column.

Average of ratios versus ratio of sums

AVERAGEX(Sales, DIVIDE(Sales[SalesAmount], Sales[SalesQuantity])) is the simple average of line prices. DIVIDE([Total Sales], [Units Sold]) is the weighted (quantity-weighted) average. They differ, and for money the weighted one is nearly always what is meant. Same for margin %: divide total margin by total sales instead of averaging row margins.

Iterating over a dimension and context transition

SUMX(VALUES(Customer[Customer]), [Total Sales]) computes the measure once per customer (the measure reference triggers context transition) and adds the results. The grain of the iteration decides the answer: iterate Product to total after a per-product rule, iterate Month for monthly averages.

Average Sales per Customer = AVERAGEX(VALUES(Customer[CustomerKey]), [Total Sales])
Average Daily Sales = AVERAGEX(VALUES('Calendar'[Date]), [Total Sales])
Peak Month Sales = MAXX(VALUES('Calendar'[Year-Month]), [Total Sales])
Capped Commission = SUMX(VALUES(Employee[Employee]), MIN([Sales Commission], 5000))

Totals that do not match the visible rows are explained by the iteration grain: the total row of a matrix is evaluated with no row filter, so a measure with an IF on a per-row condition needs an iterator over the row level to make totals add up (the "subtotal fix" in the patterns topic).

RANKX and TOPN

Product Rank = RANKX(ALLSELECTED(Product[Product]), [Total Sales], , DESC, Dense)
Top 10 Products Sales = CALCULATE([Total Sales], TOPN(10, ALLSELECTED(Product[Product]), [Total Sales], DESC))
Others = [Total Sales] - [Top 10 Products Sales]

RANKX(table, expression, [value], [order], [ties]): the table must be the set you are ranking against; use ALL or ALLSELECTED on the column in the visual so the rank is computed across the right set. In a visual that shows product rows, RANKX(ALLSELECTED(Product[Product]), ...) ranks each row among the products selected. Add ties handling (Dense or Skip). TOPN(n, table, orderBy, order, ...) returns the n rows; ties can return more than n.

Table functions that build tables

Customer Sales Table =
ADDCOLUMNS(
    SUMMARIZE(Sales, Customer[Customer]),
    "Sales", [Total Sales],
    "Orders", [Order Lines]
)
Customers Above Average =
VAR Avg = AVERAGEX(ALLSELECTED(Customer[Customer]), [Total Sales])
RETURN COUNTROWS(FILTER(ALLSELECTED(Customer[Customer]), [Total Sales] > Avg))

Counting

Text, logic and information functions you will use constantly

IF, SWITCH(TRUE(), cond1, result1, cond2, result2, default), AND/OR/NOT, COALESCE, ISBLANK, ISNUMBER, ISTEXT, ISERROR, IFERROR, CONCATENATEX(table, expr, ", "), FORMAT, LEFT/RIGHT/MID/LEN/FIND/SEARCH/SUBSTITUTE/TRIM/UPPER/LOWER, VALUE, FIXED, USERNAME, USERPRINCIPALNAME, LOOKUPVALUE, CONTAINSROW, CONTAINS, NAMEOF, SELECTEDMEASURE, SELECTEDMEASUREFORMATSTRING.

Iterator performance

Want to see it run?

Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.