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.
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
FILTER(table, condition): row by row filter.ALL(table),ALL(column),VALUES,DISTINCT,ALLSELECTED,ALLEXCEPT.ADDCOLUMNS(table, "name", expression, ...): adds computed columns (the new column is referred to as [name]).SELECTCOLUMNS(table, "name", expr, ...): projects a new set of columns.SUMMARIZE(table, groupCol, ...): groups; avoid adding aggregate expressions in SUMMARIZE, useADDCOLUMNS(SUMMARIZE(...), "Total", [Total Sales])instead.SUMMARIZECOLUMNS(groupCols, filters, "name", expr, ...): the engine behind most visuals; it removes rows where all measures are blank.GROUPBY: groups with iterators over CURRENTGROUP(); rarely better than SUMMARIZE.CROSSJOIN(t1, t2),GENERATE(t1, t2)/GENERATEALL(apply a table expression for every row),NATURALINNERJOIN,NATURALLEFTOUTERJOIN,UNION,INTERSECT,EXCEPT,GENERATESERIES(start, end, step),DATATABLE,ROW("name", value),{...}table constructors such as{("Sales", 1), ("Cost", 2)}.TOPN,SAMPLE,ADDMISSINGITEMS,ROLLUP/ROLLUPGROUP/ISSUBTOTAL(with SUMMARIZE),CALCULATETABLE(table, filters).
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
COUNTROWS(table): rows.COUNT(column)counts non-blank numbers;COUNTAnon-blank anything;DISTINCTCOUNT(column)andDISTINCTCOUNTNOBLANK.COUNTBLANK.APPROXIMATEDISTINCTCOUNTfor DirectQuery speed.- Count of customers who bought:
DISTINCTCOUNT(Sales[CustomerKey]). Count of customers in a dimension regardless of sales:COUNTROWS(Customer). They differ whenever the dimension has rows without sales. - Count of items meeting a measure condition:
COUNTROWS(FILTER(VALUES(Product[Product]), [Total Sales] > 100000)).
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
- The cost of an iterator is rows iterated times the cost of the inner expression. Iterate a dimension column (hundreds or thousands of values), not the fact table (millions), and avoid measures inside iterators over huge tables.
SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])over a fact table is fine: it is a simple row calculation the engine runs in one pass. Putting a measure or CALCULATE inside forces a context transition per row.- Prefer
COUNTROWS(FILTER(...))over nested iterators, andCALCULATE(..., column filter)overFILTER(table, ...)where possible.
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.