How to use DAX aggregate functions in Power BI with examples? Microsoft Power BI DAX provides various aggregate functions, which allow us to perform aggregations such as calculating sum, average, minimum, maximum, etc.
To demonstrate these Power BI DAX aggregate functions, we will use the data shown below. As you can see, there are 15 records in this table.
<>Power BI DAX aggregate functions
The following examples show the list of DAX aggregate functions in Power BI
Power BI DAX sum function
The sum function in Power BI DAX calculates the total or sum of records in a column. The syntax for the Power BI DAX sum function is
Measure Name = SUM (Expression or Column Name)
To demonstrate these DAX aggregation functions in Power BI, we have to use Measures. To create a measure, click the New measure option in Power BI Home tab, or Modeling tongue.
Renamed the default indicator to Sum of Sales. As you can see in the screenshot below, while typing, Power BI IntelliSense displays the suggestions.
Let me select the Power BI DAX SUM function
For Power BI DAX Aggregate Functions demonstration purpose, we will calculate the sum of sales in this table
Clicking Enter or any key creates a new Measure called SalesSum. And the final Code is
SalesSum = SUM (EmployeeSales [Sales])
Let me create a card using this measurement. See the articles Create a card and Format card to understand the steps required to create and format cards.
Average Power BI DAX function
The Power BI DAX averaging function is used to calculate the average of records in a column. The syntax for the Power BI DAX average function is
Measure Name = AVERAGE (Expression or Column Name)
As you can see in the screenshot below, we created a measure (named AVGSale) to calculate the average of the Sales column. And the Code is
AVGSale = AVERAGE (EmployeeSales [Sales])
Next, let me create a card using this average measurement.
Power BI DAX MIN function
The Power BI DAX MIN function finds the minimum value in a column. The syntax for the Power BI DAX MIN function is
Measure Name = MIN (Expression or Column Name)
As you can see in the following screenshot, we created a Measure (named MinSale) to find the Minimum Sales amount in the Sales column. And the Code is
MinSale = MIN (EmployeeSales [Sales])
Next, let me create a card using this minimum measurement.
Power BI DAX MAX function
The Power BI DAX MAX function finds the maximum value in a column. The syntax for the Power BI DAX MAX function is
Measure Name = MAX (Expression or Column Name)
Here, we create a Measure (called MaxSale) to find the Maximum Sales amount in the Sales column. And the Code is
MaxSale = MAX (EmployeeSales [Sales])
Power BI DAX COUNT function
The Power BI DAX COUNT function counts the number of numeric records in a column. The syntax for the Power BI DAX count function is
Measure Name = COUNT (Expression or Column Name)
Here, we create a measure (called CountSale) to count the number of numeric records in the New Sales column. And the Code is
CountSales = COUNT (EmployeeSales [NewSale])
Power BI DAX VAR.S function
The DAX VAR.S function finds the statistical variance of whole records. The syntax for the Power BI DAX VAR.S function is
Measure Name = VAR.S (Expression or Column Name)
Here, we create a measure (named VarSIncome) to find the statistical variance of a column of annual income. And the Code is
VarSIncome = VAR.S (EmployeeSales [YearlyIncome])
Power BI DAX VAR.P function
The Power BI DAX VAR.P function finds the statistical variance of some selected records or. The syntax for the Power BI DAX VAR.P function is
Measure Name = VAR.P (Expression or Column Name)
Here, we create a Measure (name VarSIncome) to find the statistical variance P of a column of Annual Income. And the Code is
VarPIncome = VAR.P (EmployeeSales [YearlyIncome])
Power BI DAX STDEV.P function
The DAX STDEV.P function finds the standard deviation of the selected records. The syntax for the Power BI DAX STDEV.P function is
Measure Name = STDEV.P (Expression or Column Name)
Here, we create a measure (called STDEVPIncome) to find the standard deviation of a column of annual income. And the Code is
StdevPIncome = STDEV.P (EmployeeSales [YearlyIncome])
Power BI DAX STDEV.S function
The DAX STDEV.S function finds the standard deviation of whole records. The syntax for the Power BI DAX STDEV.S function is
Measure Name = STDEV.S (Expression or Column Name)
Here, we create the Measure (named STDEVSIncome) to find the standard deviation of a column of Annual Income. And the Code is
StdevSIncome = STDEV.S (EmployeeSales [YearlyIncome])
Let me create a new card for the standard device measurement
Power BI DAX MAX function example 2
The Power BI DAX MAX function also accepts two arguments. You can use this function to find the maximum value between those two. The syntax for the Power BI DAX MAX function is:
Measure Name = MAX (Expression1, Expression2)
Here, we create a new column (called MaxValue) to find the largest value between Sales and Average Sales. I suggest you refer to the Create a column article to understand the steps required to create a column.
Power BI DAX MIN function example 2
The Power BI DAX MIN function accepts two arguments. Use this Power BI MIN function to find the smallest value between two numbers. The syntax is:
Measure Name = MIN (Expression1, Expression2)
Here, we create a new column (named MinValue) to find the smallest value between Sales and Average Sales.
Let me add those two columns Maximum Value and Minimum Value to this table.
