Power BI DAX Aggregation Functions
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]).
| Function | Returns | In Excel |
|---|---|---|
| SUM | The total of the numbers in a column | SUM |
| AVERAGE | The average (mean) of the numbers in a column | AVERAGE |
| MIN | The smallest value in a column | MIN |
| MAX | The largest value in a column | MAX |
| COUNT | The number of rows where a column is not blank | COUNT |
| COUNTA | The same as COUNT, but it can also count TRUE/FALSE values | COUNTA |
| COUNTROWS | The number of rows in a table | |
| DISTINCTCOUNT | The 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])
| Measure | Result |
|---|---|
| Total Sales | 9,416,800 |
| Total Cost | 5,124,605 |
| Units Sold | 24,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.
| Measure | Result |
|---|---|
| Avg Quantity | 8.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])
| Measure | Result |
|---|---|
| Smallest Order | 200 |
| Largest Order | 9,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])
| Measure | Result |
|---|---|
| OrderID Count | 2,740 |
| StoreID Count | 2,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)
| Measure | Result |
|---|---|
| Order Count | 2,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])
| Measure | Result |
|---|---|
| Products Sold | 10 |
| Days with Sales | 731 |
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:
| Category | Total Cost | Units Sold | Order Count | Avg Quantity | Largest Order | Days with Sales |
|---|---|---|---|---|---|---|
| Balls | 2,477,560 | 12,694 | 1,035 | 12.26 | 9,600 | 595 |
| Medicine | 2,386,540 | 8,295 | 1,110 | 7.47 | 7,500 | 622 |
| Status Healers | 260,505 | 3,499 | 595 | 5.88 | 2,000 | 433 |
| Total | 5,124,605 | 24,488 | 2,740 | 8.94 | 9,600 | 731 |
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.