What is Aggregate Functions in Power BI?
Aggregate functions in Power BI are used to calculate a set of values within a column or a table and return a single aggregated result. These functions are essential for summarizing and analyzing data, especially with large datasets. Power BI offers several built-in aggregate functions that help users create meaningful insights and visualizations from their data.
Table of contents
Key Takeaways
- Aggregate functions are essential for summarizing and analyzing data in Power BI.
- They allow you to calculate totals, averages, counts, and other summary metrics.
- Use appropriate aggregate functions based on the nature of your data and the insights you’re trying to gain.
- Combine aggregate functions with visualizations to create meaningful reports and dashboards.
Examples of Aggregate Functions in Power BI
In this section, we will apply different Aggregate functions in Power BI to create data visualization in Power BI.
#1 – SUM
In this example, we will calculate the total marks scored in a subject using the aggregate function SUM in Power BI using the Student Exam Data dataset. Student Exam Data contains scores for each student per subject. To create a visual using the SUM function in Power BI, follow the steps highlighted below:
Step 1: Open the Power BI Desktop, import the Student Exam Data dataset using the Get Data option, and click the Load button. You can choose the Transform Data option to perform data cleaning, filtering, and transformation as needed.
Once the data is loaded, you shall be able to view it by navigating to the Fields pane.
Step 2: Navigate to the Fields pane, right-click on the Student Exam Data table, and choose the New column option from the menu.
Step 3: Navigate to the Formula tab, and enter the Power BI DAX aggregate functions for the SUM function.
Here we have created a calculated column Total Marks Obtained Per Subject to calculate the total marks scored in a subject.
Total Marks Obtained Per Subject = CALCULATE(SUM(‘Student Exam Data'[Exam points]),FILTER(ALLSELECTED(‘Student Exam Data’),’Student Exam Data'[Exam name] = EARLIER(‘Student Exam Data'[Exam name])))
Note: We have used the aggregate functions DAX, such as Power BI CALCULATE, FILTER, and EARLIER along with the SUM function. It is to calculate the SUM in the context of the table filter.
- EARLIER is a filter function primarily used for nested calculations by taking specific values as input and then producing calculations based on that input.
- CALCULATE is another filter function that evaluates an expression in a modified filter context.
Step 4: Once the DAX is specified, click on the Commit icon to save the changes. This shall create a new column in the data table.
You can see the underlying values of this new column by navigating to the Data view tab.
Step 5: Navigate to the Fields pane to view the newly created column.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
#2 – AVERAGE
In this example, we will extend the previous example of the SUM function to calculate the average marks scored in a subject using the AVERAGE function. To use the AVERAGE function in Power BI, follow the steps highlighted below:
Step 1:
- Navigate to the Fields pane.
- Right-click on the Student Exam Data table.
- Choose the New column option from the menu.
Step 2: Navigate to the formula tab, and enter the Power BI aggregate functions DAX for the AVERAGE function.
Here we have created a calculated column Average Marks Obtained to calculate the average marks scored in a subject.
Average Marks Obtained = CALCULATE(AVERAGE(‘Student Exam Data'[Exam points]),FILTER(ALLSELECTED(‘Student Exam Data’),’Student Exam Data'[Exam name] = EARLIER(‘Student Exam Data'[Exam name])))
Step 3: Click the Commit icon to save the changes once the DAX is specified. It shall create a new column in the data table.
Step 4: Navigate to the Fields pane to view the newly created column.
#3 – COUNT
In this example, using the COUNT function, we will use the US_Regional_Sales_Data to calculate the total number of orders placed per customer across the stores. US_Regional_Sales_Data contains the sales information across various stores in the US. To use the COUNT function in Power BI, follow the steps highlighted below:
Step 1: Navigate to the Fields pane, right-click on the US_Regional_Sales_Data table, and choose the New column option from the menu.
Step 2: Navigate to the formula tab, enter the DAX enter the Power BI aggregate functions DAX for the COUNT function.
Here, we have created a calculated column, Total Number of Orders Placed Per Customer, to calculate the total number of orders placed per customer across various stores.
Total Number of Orders Placed Per Customer = CALCULATE(COUNT(US_Regional_Sales_Data[OrderNumber]), FILTER(US_Regional_Sales_Data,US_Regional_Sales_Data[_CustomerID] = EARLIER(US_Regional_Sales_Data[_CustomerID])))
Step 3: Click the Commit icon to save the changes once the DAX is specified. It shall create a new column in the data table.
Step 4: Navigate to the Fields pane to view the newly created column.
#4 – MIN & MAX
In this example, we will calculate the minimum and maximum marks scored in a subject using Student Exam Data. To use the MIN and MAX functions in Power BI, follow the steps below:
Step 1:
- Navigate to the Fields pane.
- Right-click on the Student Exam Data table.
- Choose the New column option from the menu.
Step 2: Navigate to the formula tab, and enter the Power BI aggregate functions DAX for the MIN function and the other one for the MAX function.
Here we have created calculated columns Minimum Marks Obtained and Maximum Marks Obtained in a subject.
Minimum Marks Obtained = CALCULATE(MIN(‘Student Exam Data'[Exam points]),FILTER(‘Student Exam Data’, ‘Student Exam Data'[Exam name] = EARLIER(‘Student Exam Data'[Exam name])))
Maximum Marks Obtained = CALCULATE(MAX(‘Student Exam Data'[Exam points]),FILTER(‘Student Exam Data’, ‘Student Exam Data'[Exam name] = EARLIER(‘Student Exam Data'[Exam name])))
Step 3: Once the Power BI aggregate functions DAX is specified, click the Commit icon to save the changes. It shall create new columns in the data table.
Step 4: Navigate to the Fields pane to view the newly created column.
#5 – DISTINCTCOUNT
In this example, we will use the US_Regional_Sales_Data dataset to calculate the distinct number of customers placed across various stores using the DISTINCTCOUNT function, one of the Power BI aggregate functions for text. US_Regional_Sales_Data contains the sales information across various stores in the US. To use the DISTINCTCOUNT function in Power BI, follow the steps highlighted below:
Step 1: Navigate to the Fields pane, right-click on the US_Regional_Sales_Data table, and choose the New column option from the menu.
Step 2: Navigate to the formula tab, and enter the Power BI aggregate functions DAX for the DISTINCTCOUNT function.
Here we have created a calculated column Distinct Number of Customers.
Distinct Number of Customers = DISTINCTCOUNT(US_Regional_Sales_Data[_CustomerID])
Step 3: Once the DAX is specified, click on the Commit icon to save the changes.
Step 4: Navigate to the Fields pane to view the newly created column.
Once you have created the columns using the aggregated functions, navigate to the Visualizations pane, choose Table visual, and drag and drop the columns to the report canvas.
It will create a visualization in Power BI, as shown below.
You can validate the numbers by creating a Pivot table in Excel and comparing the numbers generated in Power BI.
Note that there are no Power BI custom aggregation functions currently supported in Power BI.
Important Things to Note
- Aggregate functions can be applied to numeric and text columns, but the results might not be meaningful for text columns (e.g., average of text values).
- Aggregation can be affected by filters and slicers applied to the visualizations.
- Calculated columns and measures can also be used with aggregate functions to perform more complex calculations.
Frequently Asked Questions (FAQs)
SUM:
• The SUM function in Power BI is a basic aggregation function that calculates the sum of a numerical column within a table or a filter context. Its syntax is as follows:
SUM(ColumnName)
• It doesn’t involve iterating through rows; instead, it directly summarizes the values in the specified column based on the current filter context.
SUMX:
• The SUMX function, on the other hand, is an iterator function. It iterates through each row of a table, evaluates a given expression for each row within the filter context, and then sums up the results of those individual expressions.
• The Syntax of the SUMX function is as follows:
SUMX (Table, Expression)
• This function is used when you need to perform a calculation that involves considering each iteration.
The difference between COUNT and COUNTA functions are highlighted below:
COUNT:
• The COUNT function in Power BI is a basic aggregation function that counts the number of rows for a column containing non-blank values.
• The syntax of the COUNT function is as follows:
COUNT(ColumnName)
• The COUNT function considers rows that have Numbers, Dates, or Strings values.
COUNTA:
• The COUNTA function is similar to the COUNT function and count the number of rows for a column with non-blank values.
• The syntax of COUNTA function is as follows:
COUNTA(ColumnName)
Some of the key differences between AVERAGE and AVERAGEA in Power BI are highlighted below:
The differences between DISTINCTCOUNT and COUNTROWS functions are highlighted below:
DISTINCTCOUNT:
• It counts the total number of distinct values present in a column. It takes only a single argument i.e. column name and supports all the data type present in the column.
• The syntax of the DISTINCTCOUNT function is as per below:
DISTINCTCOUNT (ColumnName)
COUNTROWS:
• It counts the number of rows in the table or in a table defined by an expression as specified in the argument.
• The syntax of the COUNTROWS function is as per below:
COUNTROWS ([Table])
Download Template
This article must help understand Power BI Aggregate Functions with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to Power BI Aggregate Functions. Here we look into different aggregate function such as SUM, AVERAGE, COUNT, MIN & MAX, DISTINCTCOUNT, with examples. You can learn more from the following articles –
Leave a Reply