# Power BI Aggregate Functions Article byJeevan A Y ## Aggregate Functions in Power BI

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

Ok, let’s learn each of these Power BI aggregate functions with a practical approach now.

For eg:
Source: Power BI Aggregate Functions (wallstreetmojo.com)

### Examples of Aggregate Functions in Power BI

You can download the following workbook to use the same file as 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 are going to 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, too, 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.”
• This will ask you to name the table, so give the name as “Aggregate Functions” and put an equal sign.
• Click on the enter key, and it will create a new table like this.

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

#### #1 – SUM

Steps to use Aggregate function in Power BI are as follows.

1. So our first attempt of aggregate function is SUM, so right-click on the new table and choose the option “New Measure.” 2. Now name the measure as “1 Aggregate Function Sum”. 3. Now we are adding or summing the sales value, so open the SUM DAX function. 4. SUM is very easy; we just need to choose what is the column name that we need to sum, so choose the “Sales” column from the IntelliSense list. 5. Ok, that’s all; close the bracket and hit 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. There you go, we have total sales value, using the same measure we can get a “Sales Person” wise summary as well, for this use “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 and 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.

• Ok, 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 got average sales value; this is calculated by total sales divided by a number of line items.

• Since we are finding the number of lien items for the “Sales” column, choose the same column from the table.
• Ok, now drag and drop this new measure to the table to see each salesperson 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 = 28596.75.

#### #4 – MIN & MAX

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

• Below is the measure to find minimum value transactions.
• Use this measure to find each salesperson’s minimum transaction.
• Similarly below measure calculates the maximum value transaction.
• We get the following result.
• We get to see minimum & maximum value transactions here; with this, we can say each salesperson’s maximum transaction value is above the overall average value.

#### #5 – DISTINCTCOUNT

In excel, by removing duplicates, we can find how many unique values are there, but in Power BI we need to use the DAX function 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 there; below aggregate function will get the unique count of salespersons.
• Use card visuals to get the total count of salespersons.

So, there are totally five salespersons are there.

Note: Power BI Aggregate functions file can also be downloaded from the link below, and the final output can be viewed.

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

### Things to Remember Here

• Other aggregate functions are VAR, STD.V.
• The COUNT function counts only numerical values from the selected column, so if you want to count both numerical and alphabetical values, use the COUNTA function.

This has been a guide to Power BI Aggregate. Here we discuss how to apply 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 –

• 35+ Courses
• 120+ Hours