DAX Aggregation Functions in Power BI

Introduction

When working with Power BI, mastering DAX aggregation functions is crucial for extracting meaningful insights from your data. Aggregations help you summarize data — whether you're totaling sales, counting unique entries, or computing year-to-date performance. DAX (Data Analysis Expressions) offers a rich set of aggregation functions, divided into several categories: basic aggregators, iterator-based aggregators, filtered aggregations, and time intelligence functions.

DAX Aggregation Functions Explained

🔹 Basic Aggregators

These functions work directly on columns and return scalar values without evaluating row context.

• SUM(column) → Adds up all the numbers in the column
  E.g.: SUM(SalesData[Quantity]) returns total quantity sold.

• AVERAGE(column) → Computes the mean of all values
  E.g.: AVERAGE(SalesData[Sales]) shows the average sales per transaction.

• MIN(column) → Returns the smallest value in the column

• MAX(column) → Returns the largest value in the column

• COUNT(column) → Counts all non-blank entries

• COUNTROWS(table) → Returns the total number of rows

• DISTINCTCOUNT(column) → Counts unique/distinct values in a column

Iterator-Based Aggregators

These functions evaluate an expression row-by-row and then aggregate the results. They are more flexible and powerful than basic aggregators.

• SUMX(table, expression) → Computes the expression for each row and sums the result
  E.g.: SUMX(SalesData, SalesData[Quantity] * SalesData[UnitPrice])

• AVERAGEX(table, expression) → Average of row-level calculated values

• MAXX(table, expression) → Maximum of row-level results

• MINX(table, expression) → Minimum of row-level results

• COUNTX(table, expression) → Number of non-blank results from row-wise evaluation

• PRODUCTX(table, expression) → Product of all calculated results from rows

Filtered Aggregations

Use CALCULATE() with filters to modify context:

1. CALCULATE(SUM(SalesData[Sales]), SalesData[Region] = "North")

2. Use with FILTER(), REMOVEFILTERS(), etc.

Time-Based Aggregations

These are used for cumulative calculations over time periods.

• TOTALYTD(expression, dates_column) → Year-To-Date total

• TOTALMTD(expression, dates_column) → Month-To-Date total

• TOTALQTD(expression, dates_column) → Quarter-To-Date total

Step-by-Step Power BI Implementation
Download SalesData

✅ Step 1: Import Data

• Open Power BI → Enter Data

• Copy & paste the dataset above → Name it SalesData

---

✅ Step 2: Create Calculated Column

Go to Modeling > New Column:

dax

Sales = SalesData[Quantity] * SalesData[UnitPrice]

Step 3: Create Measures for Each Category

🔸 A. Basic Aggregators

• Total Sales:

dax

Total Sales = SUM(SalesData[Sales])

• Average Sales:

dax
Average Sales = AVERAGE(SalesData[Sales])

Max Quantity:
dax

Max Qty = MAX(SalesData[Quantity])
Distinct Products:
dax
Unique Products = DISTINCTCOUNT(SalesData[Product])

 B. Iterator Aggregators
Total Sales with SUMX:
dax

Total Sales X = SUMX(SalesData, SalesData[Quantity] * SalesData[UnitPrice])
Average Sale per Transaction:
dax

Avg Sale X = AVERAGEX(SalesData, SalesData[Sales])
Max Sale per Row:
dax
Max Row Sale = MAXX(SalesData, SalesData[Sales])

C. Filtered Aggregations
North Region Sales:
dax

North Sales = CALCULATE(SUM(SalesData[Sales]), SalesData[Region] = "North")

Sales of Product A:
dax
Product A Sales = CALCULATE(SUM(SalesData[Sales]), SalesData[Product] = "A")


🔸 D. Time-Based Aggregations
First, create or import a Date Table and relate it to SalesData[Date].
Then:
Year-to-Date Sales:
dax
YTD Sales = TOTALYTD(SUM(SalesData[Sales]), SalesData[Date])
Month-to-Date Sales:
dax
MTD Sales = TOTALMTD(SUM(SalesData[Sales]), SalesData[Date])
Quarter-to-Date Sales:
dax
QTD Sales = TOTALQTD(SUM(SalesData[Sales]), SalesData[Date])


Visualization Suggestions
Use Card visuals for each measure
Use Table visual to show all columns + Sales
Use Line Chart for YTD Sales over Date
Use Slicer for Region or Product to test CALCULATE() filters