DAX Aggregation Functions in Power BI


 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

टिप्पणी पोस्ट करा

1 टिप्पण्या

  1. Hero Vired, part of the Hero Group, offers industry-aligned learning programs that combine expert guidance, live projects, and global partnerships to prepare learners for modern careers.

    उत्तर द्याहटवा

कृपया तुमच्या प्रियजनांना लेख शेअर करा आणि तुमचा अभिप्राय जरूर नोंदवा. 🙏 🙏