# Power BI Aggregate Functions

Updated on April 17, 2024
Article byWallstreetmojo Team
Edited byWallstreetmojo Team
Reviewed byDheeraj Vaidya, CFA, FRM

## Aggregate Functions in Power BI

When we combine or summarize the numerical data, it is called “Aggregation.” The output we get from this is called “Aggregate.” So, the common aggregation functions are SUM, AVERAGE, MIN, MAX, COUNT, DISTINCTCOUNT, and so on. To use all these , we need some data that should be alpha-numerical. An aggregate function can be applied only for a numerical data set, but there is a situation where it also works for alphabetical data.

Let us learn these Power BI aggregate functions with a practical approach now.

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Power BI Aggregate Functions (wallstreetmojo.com)

–>> 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.

### Examples of Aggregate Functions in Power BI

You can download the following workbook using the same file we used in this example.

You can download this Power BI Aggregate Functions Excel Template here – Power BI Aggregate Functions Excel Template
• Below is the data we will use to demonstrate aggregate functions in Power BI. You can download the workbook and use it.
• We have already uploaded the above data table to the Power BI Desktop file. You can also upload the data table to start the DAX functions.
• We will create a new measure table to store all the aggregate measures, so go to the “Modelling” tab and click on “New Table.”
• It will ask you to name the table, so give the name “Aggregate Functions” and put an equal sign.
• Press on the “Enter” key to create a new table like this.

In this table, we will store all our new measures.

#### #1 – SUM

The steps to use the aggregate function in Power BI are as follows.

1. So, our first attempt at aggregate function is SUM. So, right-click on the new table and choose “New Measure.”

2. Now, name the measure as “1 Aggregate Function Sum.”

3. We are adding or summing the sales value, so open the SUM DAX function.

4. The SUM is very easy. First, we need to choose the column name we need to sum, so select the “Sales” column from the IntelliSense list.

5. Close the bracket and press the “Enter” key to close the first aggregate function. So, our first measure for SUM is created in the new table.

6. Now, insert the “Card” visual from the visualization list.

7. For the field of this card, drag and drop the newly created measure to get the total sales value.

8. We get the following result.

9. We have total sales value using the same measure. We can also get a “Sales Person” wise summary using a “Table” visual.

10. Drag and drop the “1. Aggregate Function Sum” from the first table and “Sales Person” from the second table.

11. We get the following result.

#### #2 – AVERAGE

For the next Power BI aggregate function, we will learn “AVERAGE.” As we all know, we use this to find the average value by the selected category.

• Again, right-click on the new table, name the measure as “2 Aggregate Function Average,” and open the AVERAGE DAX function.

For this function, we need to select the column for which we are trying to find the average.

• Now, drag and drop this new measure to the table visual.
• We get the following result.

So, for each salesperson, we have got an average sales value.

#### #3 – COUNT

We have average sales value. It is calculated by total sales divided by several line items.

• Since we are finding the number of lien items for the “Sales” column, choose the same column from the table.
• Now, drag and drop this new measure to the table to see each salesperson’s number of line items.
• We get the following result.
• For each salesperson, there are 12 line items. Now, we can understand how average values have arrived. For example, the salesperson “John” total sales are 3,43,161, and the number of line items he has is 12. So, Average = 343161 / 12 = 28,596.75.

#### #4 – MIN & MAX

Similarly, from 12 transactions of each salesperson, we need to find the minimum and the maximum transaction. So, this can be arrived at by and .

• Below is the measure to find minimum value transactions.
• Use this measure to find each salesperson’s minimum transaction.
• Similarly, the below-given measure calculates the maximum value transaction.
• We get the following result.
• We see the minimum and maximum value transactions here. With this, we can say that each salesperson’s maximum transaction value is above the average.

#### #5 – DISTINCTCOUNT

We can find how many unique values are present in Excel by removing duplicates. But in to get this number, so the DISTINCTCOUNT function can get the unique values to count from the selected column.

• For example, from the “Sales Person” column, we may need to find how many salespersons are present. Below aggregate function will get the unique count of salespersons.
• Use card visuals to get the total count of salespersons.

So, there are a total of five salespersons.

Note: You can also download the Power BI aggregate functions file from the link below. You can view the final output.

You can download this Power BI Aggregate Functions Template here – Power BI Aggregate Functions Template

### Things to Remember Here

• The other aggregate functions are VAR, STD.V.
• The COUNT function counts only numerical values from the selected column. So if we want to count numerical and alphabetical values, we must use the COUNTA function.

This article is a guide to Power BI Aggregate. Here, we discuss applying aggregate functions (SUM, AVERAGE, MIN, MAX, COUNT, DISTINCT COUNT) in Power BI, along with examples and downloadable templates. You may learn more about Power BI from the following articles: –