13.6 Aggregation Functions

By Ravindra Bagale Last updated: 25 September 2026

SUM, AVERAGE, MIN, MAX

Avg Line Value = AVERAGE(Orders[Amount])

Largest Order Line = MAX(Orders[Amount])

Fastest Delivery (mins) = MIN(Orders[Delivery Time Mins])

Slowest Delivery (mins) = MAX(Orders[Delivery Time Mins])

First Order Date = MIN(Orders[Order Date])

Last Order Date = MAX(Orders[Order Date])

MIN and MAX also accept two scalar values: MAX([Total Sales], 0).

COUNT, COUNTA, COUNTROWS, DISTINCTCOUNT, COUNTBLANK

Lines With Delivery Time = COUNT(Orders[Delivery Time Mins])   -- non-blank values

Order Lines = COUNTROWS(Orders)                 -- number of rows (order lines)

Total Orders = DISTINCTCOUNT(Orders[Order ID])  -- unique orders

Dark Stores With Orders = DISTINCTCOUNT(Orders[Store ID])

Missing Delivery Times = COUNTBLANK(Orders[Delivery Time Mins])

COUNTA counts non-blank values of any type.
COUNTROWS is generally preferred over COUNT when you just need the number of rows.
DISTINCTCOUNT counts unique values (including BLANK as one value, if present).

Iterators: SUMX, AVERAGEX (and MINX, MAXX, COUNTX)

The "X" functions take a table and an expression. They evaluate the expression for each row (row context) and then aggregate.

Gross Sales (before discount) = SUMX(Orders, Orders[Amount] + Orders[Discount])

Sales at List Price = SUMX(Orders, Orders[Quantity] * RELATED(Product[Unit Price]))

Avg Sales per Customer = AVERAGEX(VALUES(Customer[Customer ID]), [Total Sales])

Avg Items per Order = AVERAGEX(VALUES(Orders[Order ID]), [Total Quantity])

Busiest Store Orders = MAXX(VALUES(DarkStore[Store Name]), [Total Orders])

SUM vs SUMX

SUM(Orders[Amount]) is actually shorthand for SUMX(Orders, Orders[Amount]). Use SUMX when you must calculate something per row first (like Quantity × Price) and then add up. This avoids creating a calculated column.

Ravindra Bagale's Tip

Friends, pay attention: using SUM on a text column is a very common beginner error, usually because the column was never converted from text in Power Query. Fix the type at the source, not in DAX. Also remember that COUNT counts non-blank values, COUNTROWS counts rows, and DISTINCTCOUNT counts unique values. Practise, and it will feel very easy.