Aggregation functions in Power BI (using DAX) are used to summarize data. They help you calculate totals, counts, and averages from large datasets, making reports easier to analyze and understand.
These functions are commonly used inside Measures to create dynamic calculations.
SUM Function
The SUM function adds all the values in a numeric column.
Syntax
SUM(Table[Column])
Example
Total Sales = SUM(Sales[Amount])
This measure calculates the total of all values in the Amount column of the Sales table.
When to Use SUM
- To calculate total revenue
- To calculate total quantity sold
- To get total expenses or costs
COUNT Function
The COUNT function counts the number of non-blank values in a column.
Syntax
COUNT(Table[Column])
Example
Total Orders = COUNT(Sales[OrderID])
This measure counts how many non-empty OrderID values exist in the Sales table.
Related Functions
- COUNTA – Counts non-blank values (including text)
- COUNTROWS – Counts the total number of rows in a table
Example:
Total Rows = COUNTROWS(Sales)
AVERAGE Function
The AVERAGE function calculates the average (mean) of a numeric column.
Syntax
AVERAGE(Table[Column])
Example
Average Sales = AVERAGE(Sales[Amount])
This measure calculates the average sales amount.
When to Use AVERAGE
- To calculate average revenue per order
- To find average salary
- To measure average performance metrics
Important Concept: Filter Context
All aggregation functions in Measures respond to filter context. This means results will change automatically when you apply slicers, filters, or use different visuals in your report.
For example, Total Sales will show different values when filtered by year, region, or product.
Conclusion
SUM, COUNT, and AVERAGE are basic but powerful aggregation functions in DAX. They form the foundation of most business reports and dashboards. Mastering these functions is essential for creating meaningful and dynamic Power BI reports.