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

Mitrano, dhyan dya: using SUM on a text column is a khup common beginner error, usually karan 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 while COUNTROWS counts rows, and DISTINCTCOUNT counts unique values. Practice kara, mag ekdum sope vatel.