## Aggregate Functions in Power BI

Often times we need some mathematical calculations from a large amount of data to find the basic information from the data sets. So these basic mathematical calculations are called “**Aggregate Functions**”.In MS-Excel we all have done all the aggregate functions but those who are new to Power BI need guidance to learn these aggregate functions, so in this article, we will take you through how to apply aggregate functions in Power BI.

### What are Aggregate Functions?

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 excel 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 Oower BI aggregate functions with a practical approach now.

### Examples of Aggregate Functions in Power BI

Below are examples of the aggregate functions in Power BI. You can download the 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 in Power BI.

- 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

The next power BI aggregate function, we will learn is “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 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 & MAX functions.

- Below is the measure to find minimum value transactions.

- Use this measure to find each salesperson 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

- These are the commonly used aggregate functions in Power BI.
- 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, DISTINCTCOUNT) in Power BI along with examples. You may learn more about Power BI from the following articles –

- Skills to Become Power BI Professional
- Power BI Parameters Examples
- GroupBy Power BI Examples
- Using Connections in Power BI
- Power Bi Simple Tutorial

- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion