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

### Examples of Aggregate Functions in Power BI

You can download the following workbook to use the same file as we used in this example.

- 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 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 enter key and it will create a new table like this.

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

#### #1 – SUM

- So our first attempt of aggregate function is SUM, so right-click on the new table and choose the option “
**New Measure**”.

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

- Now we are adding or summing the sales value so open
**SUM DAX**function.

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

- Ok, that’s all close the bracket and hit enter key to close the first aggregate function. So, our first measure for SUM is created in the new table.

- Now insert “
**Card**” visual from the visualization list.

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

- We get the following result.

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

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

- We get the following result.

#### #2 – AVERAGE

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

- Now using COUNT function in excel we will find out how many line items are there for each salesperson.

- 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 using MIN in excel & MAX functions in excel.

- 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 DAX function to get this number, so 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.

### 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 COUNTA function.

### Recommended Articles

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
- Full Lifetime Access
- Certificate of Completion