DAX Aggregation Functions

Analogy categoryAnalogies

Basic Aggregation Functions

DAX's foundational aggregators operate directly on a column: SUM(Sales[Amount]) totals every value, AVERAGE(Sales[Amount]) computes the mean, MIN and MAX return the smallest and largest values, and COUNTROWS(Sales) counts the number of rows in a table regardless of column content. COUNT(Sales[Amount]) counts only non-blank numeric values in that column, while COUNTA(Sales[Comments]) counts non-blank values of any type, including text.

🏏

Cricket analogy: SUM(Sales[Amount]) totalling every transaction is like adding up every run scored across an entire innings, while COUNTROWS(Sales) is closer to simply counting how many balls were bowled, regardless of runs scored.

Iterator (X) Functions

Iterator functions ending in X, such as SUMX, AVERAGEX, and MAXX, take a table as their first argument and an expression as their second, evaluating that expression row by row before aggregating the results, which is essential when a calculation depends on multiple columns in the same row, like Quantity multiplied by Unit Price. SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]) computes each row's line total individually and then sums them, something a plain SUM cannot do because SUM only reads a single pre-existing column.

🏏

Cricket analogy: SUMX iterating row by row to multiply Quantity by UnitPrice is like a scorer calculating each over's runs by adding six individual ball outcomes together before totalling the whole innings.

dax

// SUM vs SUMX
Total Quantity = SUM(Sales[Quantity])

Total Revenue =
SUMX(
    Sales,
    Sales[Quantity] * Sales[UnitPrice]
)

Safe Margin % =
DIVIDE(
    SUMX(Sales, Sales[Quantity] * (Sales[UnitPrice] - Sales[UnitCost])),
    SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]),
    0
)

Counting Distinct and Blank-Safe Aggregations

DISTINCTCOUNT(Sales[CustomerID]) counts the number of unique customer values rather than every row, which differs sharply from COUNTROWS, since one customer could appear across dozens of order rows. To avoid the #DIV/0! error that a raw division like [Total Profit]/[Total Sales] can throw when sales are zero, DAX authors should wrap the division in DIVIDE([Total Profit], [Total Sales], 0), which returns a specified alternate result, here 0, whenever the denominator is blank or zero.

🏏

Cricket analogy: DISTINCTCOUNT(Sales[CustomerID]) counting unique customers is like counting how many different bowlers took at least one wicket in a match, rather than counting every wicket-taking delivery.

Choosing the Right Aggregation

Because SUM reads a single column directly through the engine's storage layer, it is generally faster than SUMX performing the same total, so SUMX should be reserved for cases where a genuine row-by-row expression is required, such as Quantity times UnitPrice, rather than used as a habitual substitute for SUM(Sales[Amount]). Overusing SUMX on simple column totals forces the DAX engine to materialize an intermediate row context unnecessarily, which can noticeably slow down measures on large fact tables with millions of rows.

🏏

Cricket analogy: Using SUM instead of SUMX for a simple total is like reading a pre-printed final score off the scoreboard instead of re-adding every single run from the ball-by-ball commentary, saving significant time.

Avoid wrapping a plain column reference in SUMX out of habit — SUMX(Sales, Sales[Amount]) does the same job as SUM(Sales[Amount]) but forces the engine to materialize an unnecessary row-by-row iteration, which can slow down measures on fact tables with tens of millions of rows.

SUM, AVERAGE, MIN, MAX, and COUNTROWS operate directly on a single stored column.
COUNT only counts non-blank numeric values; COUNTA counts non-blank values of any type.
Iterator (X) functions like SUMX and AVERAGEX evaluate an expression row-by-row before aggregating.
Use SUMX when a calculation depends on multiple columns in the same row, such as Quantity * UnitPrice.
DISTINCTCOUNT counts unique values, which differs from COUNTROWS counting every row.
Wrap divisions in DIVIDE() to avoid #DIV/0! errors and supply a safe fallback value.
Prefer plain SUM over SUMX when no row-level expression is needed, for better performance.