Tutorials › Power BI › Iterator Functions — SUMX, AVERAGEX, COUNTX

Iterator Functions — SUMX, AVERAGEX, COUNTX

Use SUMX and other X-functions to calculate row-by-row before aggregating

Iterator functions (SUMX, AVERAGEX, COUNTX, MAXX, MINX) are DAX functions that loop through a table row by row, evaluate an expression for each row, then aggregate the results. They are essential when your calculation involves a row-level formula before summing. The classic example: Revenue = Quantity × Price. If these are two separate columns, you cannot simply SUM both and multiply — that gives the wrong answer. You need SUMX to multiply row by row, then sum.

Example

SUMX — when to use it vs SUM
WRONG (gives incorrect result):
  Revenue = SUM(Sales[Quantity]) * SUM(Sales[Price])
  → Multiplies TOTALS, not row-by-row values

  Example:
  Row 1: Qty=2, Price=500 → should contribute 1000
  Row 2: Qty=5, Price=200 → should contribute 1000
  Correct total: 2000

  SUM(Qty) * SUM(Price) = 7 * 700 = 4900  ← WRONG!

CORRECT with SUMX:
  Revenue = SUMX(Sales, Sales[Quantity] * Sales[Price])
  → Row 1: 2 * 500 = 1000
  → Row 2: 5 * 200 = 1000
  → Sum = 2000 ✓

AVERAGEX — average of a row-level calculation:
  Avg Profit Margin =
    AVERAGEX(
      Sales,
      DIVIDE(Sales[Profit], Sales[Revenue])
    )
  → Calculates margin for each row, then averages

COUNTX — count rows where expression is non-blank:
  Orders With Discount =
    COUNTX(FILTER(Sales, Sales[Discount] > 0), Sales[OrderID])
💡 Use SUMX whenever your measure involves multiplying or dividing two columns before summing.

Key Points

  • ✓SUMX syntax: SUMX(table, expression) — table is iterated, expression is evaluated per row
  • ✓SUMX is slower than SUM on large tables — use a calculated column for static row calculations if performance is critical
  • ✓The first argument to any X function can be FILTER(table, condition) — "sum only matching rows"
  • ✓AVERAGEX gives a different result than AVERAGE when the value is a calculation (not a column)
  • ✓Every X function has a non-X equivalent — use X when row-level logic is needed, non-X otherwise

Practice Question

Your Sales table has Quantity and UnitPrice columns. Which formula correctly calculates total revenue (sum of Quantity × UnitPrice per row)?