Power BI Aggregate Functions

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.

Power-BI-Aggregate.png

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)

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.
Power BI Aggregate - Data
  • 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.
 Table Data
  • We will create a new measure table to store all the aggregate measures, so go to the Modelling tab and click on “New Table.”
Power BI Aggregate -New Table
  • This will ask you to name the table, so give the name as “Aggregate Functions” and put an equal sign.
 Aggregate Functions Column
  • Click on the enter key, and it will create a new table like this.
Power BI Aggregate - Aggregate Functions Table

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

    Power BI Aggregate - New Measure

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

    Power BI Aggregate - 1 AF SUM

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

    Power BI Aggregate - SUM 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.

    Power BI Aggregate - SUM Function with Sales column

  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.


    Power BI Aggregate - 1 AF SUM Table

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

    Power BI Aggregate - Insert Card

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

    Power BI Aggregate - Drag SUM

  8. We get the following result.

    Power BI Aggregate - Sum Function 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.


    Power BI Aggregate - Insert Table

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

    Power BI Aggregate - Drag Sales Person And 1AF Sum

  11. We get the following result.

    Power BI Aggregate - Result (SUM)

#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.
Power BI Aggregate - Average

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

Average Query
  • Ok, now drag and drop this new measure to the table visual.
Power BI Aggregate - Drag Average Function
  • We get the following result.
Average 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.

Power BI Aggregate - Count
  • Since we are finding the number of lien items for the “Sales” column, choose the same column from the table.
Count Query
  • Ok, now drag and drop this new measure to the table to see each salesperson number of line items.
Power BI Aggregate - Drag Count
  • We get the following result.
 Count 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.
Power BI Aggregate - Count Example

#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 excelUsing MIN In ExcelIn Excel, the MIN function is categorized as a statistical function. It finds and returns the minimum value from a given set of data/array.read more & MAX functions in excelMAX Functions In ExcelThe MAX Formula in Excel is used to calculate the maximum value from a set of data/array. It counts numbers but ignores empty cells, text, the logical values TRUE and FALSE, and text values.read more.

  • Below is the measure to find minimum value transactions.
Min
  • Use this measure to find each salesperson’s minimum transaction.
Power BI Aggregate - MIN Result
  • Similarly below measure calculates the maximum value transaction.
 MAX
  • We get the following result.
Power BI Aggregate -Max 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.
 DistinctCount
  • Use card visuals to get the total count of salespersons.
Power BI Aggregate - DistinctCount Result

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