13.6 SUM, SUMX and Other DAX Aggregation Functions with Example

By Ravindra Bagale Last updated: 6 October 2026

Quick answer: the short, self-contained version of this lesson is the guide DAX COUNT, COUNTA, DISTINCTCOUNT and COUNTROWS.

SUM vs SUMX in Power BI with example — in 30 seconds

What: SUM adds one column. SUMX goes row by row, calculates something, then adds the results.

Why: Qty × Price must be multiplied on each row first. Multiplying two totals gives a wrong answer.

Tiny example: SUMX gives ₹205 (right). SUM(Qty) × SUM(Price) gives ₹810 (wrong).

First look at the data (before → after):

SUM vs SUMX in Power BI with example: before and after tables, fictional Blinkit Maharashtra data

Same before / after tables as text

Before — Order lines

Product	Qty	Unit Price (₹)	Qty × Price
Gokul Milk	2	30	60
Misal Masala	1	85	85
Ladi Pav	3	20	60

About this data: This table uses columns: Product, Qty, Unit Price (₹), Qty × Price. This table uses columns: Measure, Value. Values shown are the lesson examples already on this page (not a new dataset).

Formula / click path

Sales SUMX = SUMX(Orders, Orders[Qty] * Orders[Unit Price])
Wrong = SUM(Orders[Qty]) * SUM(Orders[Unit Price])

After — Results

Measure	Value
Sales SUMX (row by row)	₹205
SUM(Qty) × SUM(Price) — wrong	6 × 135 = ₹810

Do it step by step:

Click Home › New measure.
Type the SUMX formula. Press Enter.
Make a card with Sales SUMX. It shows ₹205.
Make the wrong measure too, only to see ₹810. Then delete it.

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.

Loading DAX practice tasks...

Total fees (Easy): Write Total Fee = SUM of Visits[Fee].
Average waiting time (Easy): Write Avg Wait = AVERAGE of Visits[WaitMins].
Longest wait (Easy): Write Max Wait = largest WaitMins.
Number of visits (Easy): Write Visit Count = COUNTROWS of Visits.
Unique patients (Medium): Write Patients = number of different Patient names.
Count text values (Medium): Write Insured Values = COUNTA of Visits[Insured].
Cheapest fee (Medium): Write Min Fee = smallest Visits[Fee].
Fees by department (Medium): The box shows a row per Doctors Department. Write Total Fee = the sum of Visits[Fee].
Fee per waiting minute (Hard): Write Fee per Min = AVERAGEX over Visits of Fee ÷ WaitMins.
Highest daily fee (Hard): Write Best Day Fee = the highest total fee earned on a single visit date.

See all Power BI practice tasks

Ravi Sir's Cards for this lesson

Aggregation functions. Each aggregation answers a different question. Picking the wrong one gives a wrong number.

Revision card What is the difference between SUM and SUMX? SUM adds up one column. SUMX is an iterator: it goes row by row through a table, computes an expression for each row, then adds the results.

What is the difference between SUM and SUMX? SUM adds up one column. SUMX is an iterator: it goes row by row through a table, computes an expression for each row, then adds the results.

Interview card

मराठी cards