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 aggregate functionsAggregate FunctionsAGGREGATE Function in excel returns the aggregate of a given data table or data lists.read more, 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.

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)

–>> 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.
Power BI Aggregate - Data
  • 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.
 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
  • It will ask you to name the table, so give the name “Aggregate Functions” and put an equal sign.
 Aggregate Functions Column
  • Press on the “Enter” key to create a new table like this.
Power BI Aggregate - Aggregate Functions Table

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


    Power BI Aggregate - New Measure

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


    Power BI Aggregate - 1 AF SUM

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


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


    Power BI Aggregate - SUM Function with Sales column

  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.


    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. We have total sales value using the same measure. We can also get a “Sales Person” wise summary using a “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, 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
  • 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 average sales value. It is calculated by total sales divided by several 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
  • Now, drag and drop this new measure to the table to see each salesperson’s 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 = 28,596.75.
Power BI Aggregate - Count Example

#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 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 and 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, the below-given measure calculates the maximum value transaction.
 MAX
  • We get the following result.
Power BI Aggregate -Max 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 Power BI we need to use the DAX functionPower BI We Need To Use The DAX FunctionIn Power Bi, DAX stands for Data Analysis and Expression and is a functional language that represents all of the functions in Excel and Power BI. Formulas in Power BI can be created using the DAX language.read more 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.
 DistinctCount
  • Use card visuals to get the total count of salespersons.
Power BI Aggregate - DistinctCount Result

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