Power BI Aggregate Functions

In this Power BI Tutorial, we will learn about Power BI Aggregate Functions. Also, we will learn about:

• What is an Aggregate?
• Types of aggregate functions
• Types of DAX aggregation functions
• Numeric field aggregation in Power BI
• Examples of aggregate function in Power BI
• Text field aggrgation in Power BI
• Count Power BI function
• Sum function in Power BI
• Average in Power BI
• Minimum in Powerr BI
• Maximum function in Power BI

Intoduction to Power bi aggregation

In our data, when we combine the values mathematically such as sum, average, maximum, count, etc., these combine values in our data known as aggregating and the result of the operational mathematical operations known as aggregate.

A Power BI DAX function provides a very essential and useful category function called ‘Aggregate function’. Aggregate functions return a scalar value by applying an aggregation function to a column.

Whereas the Power bi aggregation function calculates the scalar value such as count, sum, average minimum, and maximum for all rows in a column or table as defined by the expression.

Types of aggregate functions

In Power BI there are several aggregate functions are available, Which we are discussed below with a tabular expression:

Function	Description
AVERAGE	Returns the average of all the numbers in a column.
AVERAGEA	Returns the average of all the values in a column. Handles text and non-numeric values.
COUNT	Counts the number of numeric values in a column.
COUNTA	Counts the number of values in a column that is not empty.
MAX	Returns the largest numeric value in a column.
MAXX	Returns the largest value from a set of expressions evaluated over a table.
MIN	Returns the smallest numeric value in a column.
MINX	Returns the smallest value from a set of expressions evaluated over a table.
SUM	Add all the numbers in a column.

Read, Power bi measure examples (20 useful examples)

Types of DAX aggregation functions

Now we will discuss the types of DAX aggregation functions available in Power BI.

Function	Description
SUMX	Returns the sum of an expression evaluated for each row in a table.
AVERAGEX	Evaluates the average of a set of expressions over a table.
COUNTX	Counts the total number of rows in a table.
COUNTAX	Counts a set of expressions calculated over a table.
COUNTROWS	Counts the number of rows in a table.
COUNTBLANK	Counts the number of blanks in a table.

Here we will see the power bi aggregation function and description with example.

Read Power bi measure count with filter

COUNT()

In power bi, the count function counts the number of cells in a column, that contains the non-blank values, numbers, strings, dates. It will return the whole value.

Syntax:

COUNT(<column name>

Example of COUNT DAX function:

COUNTA()

In power bi, the COUNTA function counts the number of cells in a column that are no empty or blank. It returns the whole number.

When the COUNTA() does not find any rows to count, the function will return a blank.

Syntax:

COUNTA(<column>)

Example of COUNTA DAX function

Read How to get selected value from Slicer in Power BI

COUNTAX()

In power bi, the COUNTAX function counts the nonblank output while evaluating the output of an expression over a table. It returns a whole number.

It works similar to the COUNTA(), but COUNTAX() is used to iterate through the rows in a table and also count rows, where the specified expression results in non-blank rows.

Syntax:

COUNTAX(<Table>,<expression>)  

Example of COUNTAX function

COUNTBLANK()

In power bi, the COUNTBLANK() counts the number of blank cells in a column. It will return the whole number. If in a column no rows are found that meet the condition, then blanks are returned.

Syntax:

COUNTBLANK(<column>)

Example of COUNTBLANK DAX function:

Read Power BI average function with Examples

COUNTROWS()

In Power bi, the COUNTROWS() counts the number of rows in a specified table define by an expression. Its returns type is the whole number.

The COUNTROWS() can be used to cunt the number of rows in a base table, but more often this function is used to count the number of rows that result from the filtering table.

Then the function returns a blank when there are no rows to aggregate. If there are rows but none of them meet the specified criteria, the function return 0.

Syntax:

COUNTROWS([<table>])

Example of the COUNTROWS Dax function

COUNTX()

In Power bi, the COUNTX() counts the number of rows that contains a non-blank value or an expression that evaluates to a non-blank value, when evaluating an expression over a table. It returns an integer.

In Power bi, the COUNTX function takes two arguments, the first argument must always be a table or any expression that returns a table. The second argument is the column or the expression that is searched by COUNTX.

The COUNTX function counts only values, dates, or strings. If the COUNTX() finds no rows to count, it returns a blank.

The syntax for the COUNTX function:

COUNTX(<table>,<expression>) 

Example of COUNTX function

Read Power BI Measure multiply with examples

DISTINCTCOUNT

In Power Bi, the DISTINCTCOUNT() counts the number of distinct values in a column. It returns the number distinct value in a column.

In this function the only argument that allows is a column, you can use the column to contain any type of data.

If the function finds no rows to count, then it returns blank, or else it will return the count of distinct values.

The syntax for Distinctcount()

DISTINCTCOUNT(<column>)  

The example of distinct count function

DISTINCTCOUNTNOBLANK()

In Power bi, DISTINCTCOUNTNOBLANK () also count the number of distinct values in a column, the column contains the values to be counted. It returns the number of distinct values in the column. And this function does not include the BLANK value.

The syntax for the DISTINCTCOUNTNOBLANK function.

DISTINCTCOUNTNOBLANK(<column>)

The example of the DISTINCTCOUNTNOBLANK function.

Read Power bi sort by measure

Average()

In Power Bi, the average() returns the average or arithmetic mean of all numbers in a column.

This function returns a decimal number that represents the arithmetic mean of the numbers in the column

The Average function takes the specified column as an argument and finds the average of the values in that column

If the column contains the non-numeric values are handled in the following ways:

• Column contains text, then no aggregation can be performed, and the function return blank
• Column contains logical value or the empty cells, those values are ignored.
• Cells with the value as zero not included.

If in the column, the cell contains 0 then it gets added to the sum of the number, and the rows are counted among the number of rows used as the divisor. If the cells get blank, the row is not counted.

The syntax for the Average function

AVERAGE(<column>)

The example of an average function:

AVERAGEA()

In Power Bi, the AVERAGEA() returns the average( arithmetic mean) of the values in a column, and also it handles text and numeric value

This function takes a column and average the numbers in it, but also handles non-numeric data types according to the following rules:

• If value is true then it count as 1.
• If values is false then it count as 0
• If value contains non numeric text, it count as 0
• Empty text count as 0.

If there are no rows to aggregate, then also this function return blank.

The syntax of the Average()

AVERAGEA(<column>)

The example of AVERAGEA()

Read Power bi measure divide + 8 examples

AVERAGEX()

In the Power Bi, the AVERAGEX() calculates the average (arithmetic mean) of a set of expressions evaluated over a table. It returns a decimal number

This function enables you to evaluate the expression for each row of a table and then take the resulting set of values and calculate its arithmetic mean.

The AVERAGEX follows the same rule as average, we cannot include non-numeric or non-cell, and both the table and expression are required.

The syntax of AVERAGEX ()

AVERAGEX(<table>, <expression>)

The example of AVERAGEX ()

MAX()

In power bi, the MAX() returns the largest value in a column or between two scalar functions.

By using this function, when we compare two expressions, blank is treated as 0 when comparing. If we compare 1 with blank() using Max(), it returns 1.

And if we compare -1 with blank() using Max(), it return 0. And if both arguments are blank, MAX returns a blank.

The syntax of MAX() expression

MAX(<column>)

Max(<expression1>, <expression2>)

Example 1 of max()

Example 2 of max()

Read Power BI Measure Date + Examples

MAXA()

In power bi, the MAXA() returns the largest value in a column.

This function takes as argument a column and looks for the largest value among the following types of values:

• Numbers
• Dates

If the rows that evaluate to true, count as 1, and if the rows to evaluate to false, count as 0. Empty cells are ignored by this function.

And if the column contains no value then it returns 0.

The syntax of MAXA()

MAXA(<column>)

The example1 of MaxA()

Example 2 of MAXA()

MAXX()

In power bi, the MAXX() evaluates an expression for each row of a table and returns the largest value.

In the MAXX(), the table argument can be a table name or an expression that evaluates to a table, and the second argument indicates the expression to be evaluated for each row of the table

The values that are evaluated, are:

• Numbers
• Text
• Dates

This function skipped the blank values, True or false are not supported.

The syntax for the MAXX()

MAXX(<Table>,<expression>)

The example of MAXX()

Read Power BI Measure Filter

MIN()

In Power bi, the MIN() returns the smallest value in a column or between two scalar expressions.

This function takes a column or two expressions as an argument and returns the smallest value and the following types of values in the column are counted:

• Numbers
• Texts
• Dates
• Blanks

When comparing expressions using MIN(), blank is treated as 0. If we compare 1, Blank() it returns as 0, and if we compare -1 with Blank(), it returns -1.

If the expression returns a value that is not allowed, MIN returns an error. Logical values are not supported i.e true and false.

The syntax for MIN()

MIN(<column>)

MIN(<Expression1>,<Expression2>)

The example of the MIN()

MINA()

In Power Bi, the MINA() returns the smallest value in a column. The MINA() takes the column contains numbers as an argument and determines the smallest value as follows

• If the column contains no value, then the function returns 0.
• Rows in the column that evaluates to logical values, such as true and false, if the value is true, it returns 1 and if the value as false, it return as 0.
• Empty cells are ignored by function.

The syntax of MINA()

MINA(<column>)

The example of MINA()

MINX()

In Power Bi, the MINX() returns the smallest value that results from evaluating an expression for each row of a table.

This function takes the first argument as a table or an expression that returns a table. The second argument of this function contains the expression that is evaluated for each row of the table.

While evaluating blank values are skipped and true/ false values are not supported.

The syntax of MINX()

MINX(<table>,<expression>)

The example of MINX()

Read Power bi measure switch statement with examples

SUM()

In Power bi, the SUM() adds all the numbers in a column. It returns a decimal number.

If we want to filter the values that you are summing, we can use the SUMX function and specify an expression to sum over.

The Syntax of SUM()

SUM(<column>)

The example of SUM()

SUMX()

In Power bi, the SUMX function returns the sum of an expression evaluated for each row in a table. It returns the decimal number.

This function takes as its first argument a table or an expression that returns a table and the second argument is a column that contains the numbers you want to sum, or an expression that evaluates to a column.

This function counts only the numbers in the column, blanks, text, and logical values are ignored.

The syntax for the SUMX()

SUMX(<Table>,< Expression>)

The example of the SUMX()

Numeric field aggregate functions in Power BI

By default, some ways are available to aggregating a field in a Power BI.

For example, here we take a sample data having a textual value and a numerical value field. Using this data we will create a table chart on the Power BI Report page.

On the sales, click on the down arrow to see the default aggregate options:

• Don’t summarize- Each value in showing separately.
• Count(Distinct)- Counts the number of different values in the field.
• Median- Show the middle value.
• Variance- It calculates the average squared difference of the mean.
• Standard deviation- It is a mathematical number used to tell how measurements for a group spread out from the average.

If we select the option ‘Sum’, a green tick mark will appear on that. and the chart will look like this:

Unlike Sum, we can evaluate the rest of the aggregate functions. The result will be:

• Average- 115.00
• Minimum- 70
• Maximum- 150
• Count(Distinct)- 4
• Count- 6
• Standard deviation- 28.72
• Median- 110
• variance- 825.00

Read Power bi date filter

Examples of aggregate DAX function in Power BI

Power BI DAX sum function with example

Power BI DAX SUM function is used to calculate the total of all values in a column. To evaluate this DAX SUM function, we have to create a measure.

On that, we can put SUM’s expression. The syntax is:

Measure name = SUM(Column name)

Here we used this measure(i.e. Sales_sum) in a card to show the value.

Power BI DAX Average function with example

Power BI DAX Average function is used to evaluate the average of records present in a column. The syntax is:

Measure name = AVERAGE(Column name)

Here we created a Measure (named as Sales_Average), which is used to calculate the average of the column (i.e. Products[sales]). We used the card to show the average value.

Read Power bi measure examples

Power BI DAX MAX function with example-1

The Power BI DAX MAX function is used to evaluate the largest numeric value of a specific column. For this function the syntax is:

Measure name = MAX(Column name)

Here we created a measure as Sales_Max and the column name is sales. To visualize the value we used a Card.

Power BI DAX MAX function with example-2

The Power BI DAX MAX function also allows calculating the largest number between two arguments. To implement this, the syntax is:

Measure name = MAX(scalar1, scalar2)

The above chart, showing the maximum value between the average and min value.

Power BI DAX MIN function with example-1

The Power BI DAX MIN function is used to evaluate the smallest numeric value of a specific column. For this function the syntax is:

Measure name = MIN(Column name or expression)

Here we used this measure(i.e. Sales_Min) in a card to show the value.

Read Power BI MAX and MIN function with Examples

Power BI DAX MIN function with example-2

The Power BI DAX MIN function is used to evaluate the smallest numeric value between two arguments. For this function the syntax is:

Measure name= MIN(column1, column2)

Here it is showing the smallest value in between the average and min value.

Power BI DAX variance(VAR.P) function with example

The Power BI DAX variance function is used to calculate the statistical variance of the selected column. The syntax is:

Measure name = VAR.P(column name)

The above chart, showing the variance of a selected column (i.e sales from the Products table).

Power BI DAX Standard deviation (STDEV.P) function with example

This function calculates the standard deviation based on the entire population given as an argument. To evaluate the standard deviation, the syntax is:

Measure name = STDEV.P(column name)

Here we created a new measure as Sales_variance and to show the variance of the sales column, we used a card.

This is how we can use the DAX aggregate function in Power BI.

Read Power BI Slicer between two dates

Text field aggrgation in Power BI

We can also aggregate a text field. For example, here we will see how to aggregate the Product column(textual column) and set it to First, Last, Count(Distinct), and Count.

To implement the aggregation on a text field, we have to follow this step-by-step guide.

Step-1:

Here we are using the table chart having the product column in the value field. Usually, the field is used for a numeric field. But as the Product column is a text field so it visualizes with a single column.

Step-2:

We can see the aggregate options by clicking on the down arrow of Product.

Here, the Power BI recognizes the field as a text field, so by default, it sets to Don’t summarize, and presents the data with a single column.

Step-3:

Let’s change the aggregate from Don’t summarize to First. Then it will show the first value of the column(i.e Product)

Step-4:

Similarly, when we aggregate to last it will show the last value of the product.

Step-5:

If we select aggregate to count, it will count the total number of products.

This is how we can do aggregate on the text field.

You may like the following Power BI tutorials:

• Microsoft Power BI KPI Visual
• Power bi slicer contains
• Power bi slicer multiple columns with examples
• Power bi slicer checkbox
• Microsoft Power BI Card – How to use
• How to create a table in Power bi dashboard
• Power bi sum group by multiple columns

Conclusion

From this Power BI Tutorial, we learned all about Power BI Aggregate Functions. Also, we discussed:

• What is an Aggregate and it’s type?
• What are the types of DAX aggregation functions?
• How to aggregate Numeric field in Power BI?
• Examples of aggregate function in Power BI?
• How to aggrgate text field in Power BI?