Menu
×
   ❮   
HTML CSS JAVASCRIPT SQL PYTHON JAVA PHP C C++ C# AWS W3.CSS HOW TO BOOTSTRAP REACT MYSQL JQUERY EXCEL XML DJANGO NUMPY PANDAS NODEJS DSA TYPESCRIPT ANGULAR ANGULARJS GIT POSTGRESQL MONGODB ASP AI R GO KOTLIN SWIFT SASS VUE GEN AI SCIPY CYBERSECURITY DATA SCIENCE INTRO TO PROGRAMMING HTML & CSS BASH RUST TOOLS MATH

Power BI DAX Aggregation Functions


Share

What is an Aggregation?

An aggregation function takes many values and returns one: a sum, an average, a count, the smallest or the largest value.

Most measures start with an aggregation.

You already used one: Total Sales = SUM(Sales[Amount]).

FunctionReturnsIn Excel
SUMThe total of the numbers in a columnSUM
AVERAGEThe average (mean) of the numbers in a columnAVERAGE
MINThe smallest value in a columnMIN
MAXThe largest value in a columnMAX
COUNTThe number of rows where a column is not blankCOUNT
COUNTAThe same as COUNT, but it can also count TRUE/FALSE valuesCOUNTA
COUNTROWSThe number of rows in a table
DISTINCTCOUNTThe number of different values in a column

This page uses the Poke Mart model described in DAX Introduction, with the Amount and Cost columns from DAX RELATED and the Total Sales measure from DAX Measures.

Create each measure on this page in the Sales table, as you did in DAX Measures.

Each result below is the value for the whole shop.

A card shows the same value, shortened for big numbers.


SUM

SUM adds up all the numbers in a column.

Syntax

SUM(column)

Create two more SUM measures.

Total Cost adds up the Cost column:

Example

Total Cost = SUM(Sales[Cost])

Units Sold adds up the quantities:

Example

Units Sold = SUM(Sales[Quantity])
MeasureResult
Total Sales9,416,800
Total Cost5,124,605
Units Sold24,488

The Poke Mart sold 24,488 items in two years.

SUM only takes a column.

To add up a calculation, such as the quantity times the price, without a calculated column, use SUMX.

You learn it in DAX SUMX.


AVERAGE

AVERAGE returns the average of the numbers in a column.

Syntax

AVERAGE(column)

Example

Avg Quantity = AVERAGE(Sales[Quantity])

Format this measure as a decimal number with 2 decimal places.

MeasureResult
Avg Quantity8.94

An average order has almost 9 items.

AVERAGE skips blank values, but it includes zeros.


MIN and MAX

MIN returns the smallest value in a column, and MAX the largest.

Syntax

MIN(column)
MAX(column)

Example

Smallest Order = MIN(Sales[Amount])

Example

Largest Order = MAX(Sales[Amount])
MeasureResult
Smallest Order200
Largest Order9,600

The largest single order is worth 9,600: 8 Ultra Balls at 1,200 each.

MIN and MAX also work on dates and text.

For example, MIN('Date'[Date]) returns the first date in the Date table.

Both can also compare two values: MAX([Total Sales], [Total Cost]) returns the larger of the two measures.



COUNT and COUNTA

COUNT counts the rows where a column is not blank.

Syntax

COUNT(column)
COUNTA(column)

Example

OrderID Count = COUNT(Sales[OrderID])

COUNTA does the same, but it can also count a column of TRUE/FALSE values:

Example

StoreID Count = COUNTA(Sales[StoreID])
MeasureResult
OrderID Count2,740
StoreID Count2,740

Both return 2,740, because no order is missing its OrderID or its StoreID.

Note: In Excel, COUNT counts numbers only.

In DAX, COUNT counts numbers, dates and text.

The only difference from COUNTA is that COUNTA also counts TRUE/FALSE values.


COUNTROWS

COUNTROWS counts the rows of a table.

It does not look at any column, so blank values do not matter.

Syntax

COUNTROWS(table)

Example

Order Count = COUNTROWS(Sales)
MeasureResult
Order Count2,740

Each row of Sales is one order, so Order Count is the number of orders.

When you want to count rows, Microsoft recommends COUNTROWS over COUNT.

It is faster, it is not affected by blanks, and the formula says clearly what it counts.


DISTINCTCOUNT

DISTINCTCOUNT counts the different values in a column.

Each value is counted once, no matter how often it appears.

Syntax

DISTINCTCOUNT(column)

Example

Products Sold = DISTINCTCOUNT(Sales[ProductID])

Example

Days with Sales = DISTINCTCOUNT(Sales[Date])
MeasureResult
Products Sold10
Days with Sales731

All 10 products were sold.

And 2024 and 2025 have 731 days together, so the Poke Mart sold something every single day.

If a column contains blanks, DISTINCTCOUNT counts the blank as one value too.


All the Measures by Category

Put Category and the measures in a table visual.

Each measure is calculated again for each category:

CategoryTotal CostUnits SoldOrder CountAvg QuantityLargest OrderDays with Sales
Balls2,477,56012,6941,03512.269,600595
Medicine2,386,5408,2951,1107.477,500622
Status Healers260,5053,4995955.882,000433
Total5,124,60524,4882,7408.949,600731

Balls sell the most units per order.

Medicine has the most orders.

Now look at the Total row.

Units Sold adds up: 12,694 + 8,295 + 3,499 = 24,488. But Days with Sales does not: 595 + 622 + 433 = 1,650, while the total is 731. On most days, the shop sold products from more than one category.

In the Total row, each day is counted only once.

The Total row is not made by adding up the rows above it.

The measure is calculated again, over all the rows.

For SUM and COUNT the result is the same as adding up, but for averages, maximums and distinct counts it is not.


Which Measures to Keep

Later chapters use Total Sales, Total Cost, Order Count and Units Sold. Keep these four.

The other measures on this page were examples.

You can keep them or delete them.



Free account

Track your progress

XP earned 0 Day streak 0 Spaces 0 League —

New

Learn in Adventure Mode W3Schools Adventure App

Coding fundamentals as bite-sized lessons and challenges.

×

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
sales@w3schools.com

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail:
help@w3schools.com

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookies and privacy policy.

Copyright 1999-2026 by Refsnes Data. All Rights Reserved. W3Schools is Powered by W3.CSS.