DAX Aggregate Functions in Power BI

Microsoft Power BI is a tool that helps businesses to analyze data and create interactive reports and visualizations. It can connect to various data sources such as Excel, SQL databases, cloud services, etc and can perform aggregate functions on them for analysis. In this article we will learn about DAX aggregate function.

DAX Aggregate Functions

Aggregating data means performing mathematical operations such as adding, averaging, finding the maximum or minimum or counting values. These operations help summarize data to make it easier to analyze. Power BI can automatically calculate aggregates like sum, average, count, maximum and minimum. We will learn about them using various example and the dataset we are going to use can be downloaded from here: [SLS Order Details_Master,  SLS Order Details_Product Master, Sheet1 ]

1. DAX Average Function

One of the common aggregate functions in Power BI is the Average function. This function calculates the average of all the numbers in a specified column. It adds up all the values in that column and divides the sum by the number of rows.

Syntax: AVERAGE(<column>)

Let' s see the below aggregation example to find the average number of orders received per day.

2. DAX AverageA Function

The AVERAGEA function in DAX is similar to the AVERAGE function but with one key difference. it handles non-numeric values in a special way. While AVERAGE only considers numeric data AVERAGEA can also calculate averages with data that isn't purely numeric. The AVERAGEA function works with different types of data:

• TRUE values are counted as 1.
• FALSE values are counted as 0.
• Empty text ("") is treated as 0.
• Any non-numeric text values are also treated as 0.

Syntax: AVERAGEA(<column>)

The below image example shows a DAX measure in Power BI that calculates the average unit price (in INR per unit) from the 'SLS Order Details_Master' table.

3. DAX AverageX Function

It calculates the average (arithmetic mean) of a set of expressions evaluated over a table.

Syntax: AVERAGEX(<table>,<expression>)

The below image example shows a DAX measure in Power BI that calculates the average of the product of Unit Price and Tax Amount for each record in the 'SLS Order Details_Master' table using the AVERAGEX function.

4. DAX Count Function

The COUNT function in Power BI counts the number of rows with values in a specified column. It works with numbers, dates and strings but ignores empty or null values.

Syntax: COUNT(<column>)

The below image shows a DAX measure in Power BI that counts the number of non-blank entries in the [Book Date (dd-mm-yyyy)] column of the 'SLS Order Details_Master' table using the COUNT function.

5. DAX CountA Function

It determines how many rows in the chosen column have non-blank values. The function returns a blank if it cannot locate any rows to count.

Syntax: COUNTA(<column>)

Below image example show count the number of products that have manufacturing locations specified on them.

6. DAX CountX Function

The COUNTX function in DAX counts the rows in a table where a given expression evaluates to a number. It requires two arguments: the first is the table or expression returning a table and the second is the column or expression to evaluate.

Syntax:

COUNTX(<table, expression>)
COUNTX(FILTER(<table, expression>, [column of which counts needs to be returned]))

The image below shows a DAX measure in Power BI that counts the number of non-blank [Product Quantity] entries where the [Unit Price (INR/Unit)] is greater than 1000, using COUNTX and FILTER functions.

7. DAX CountAX Function

Counts non-blank results when evaluating the result of an expression over a table.

When determining the outcome of an expression over a table, the COUNTAX function counts results that are not blank. In other words, it functions just like the COUNTA function but is used to loop across table rows and count the rows where the supplied expressions return a result that is not blank.

Syntax: COUNTAX(<table, expression>)

8. DAX CountBlank Function

The COUNTBLANK function in DAX counts the number of blank cells in a column. It only accepts a single argument which is the column you want to check. This function treats cells with no value as blank but does not count cells with a zero value since zero is a valid numeric value. If you want to know how many empty cells are in a column use COUNTBLANK.

Syntax: COUNTBLANK(<column>)

The image below shows a DAX measure in Power BI that counts the number of blank (empty) entries in the [Product Manufacturer] column of the 'SLS Order Details_Product Master' table using the COUNTBLANK function.

9. DAX CountRows Function

It determines how many rows there are in the supplied table or a table that has been defined using an expression.

Syntax: COUNTROWS([<table>])

The below image shows a DAX measure in Power BI where COUNTROWS is used to count the total number of rows present in the 'SLS Order Details_Master' table resulting in a count of 2986 rows.

10. DAX DistinctCount Function

The DISTINCTCOUNT function in DAX counts how many different values are in a column. It only works with one column at a time. If there are no values in the column, it will return a blank. Otherwise, it gives the total number of unique values in the column.

Syntax: DISTINCTCOUNT(<column>)

11. DAX DistinctCountNoBlank

It counts the number of distinct values in a column.

Syntax: DISTICTCOUNTNOBLANK(<column>)

12. DAX Max Function

It returns the largest numeric value in a column or between two scalar expressions.

Syntax:

MAX(<column>) /
MAX(<expression1, expression2>)

13. DAX MaxA Function

It returns the largest value in a column.

Syntax: MAXA(<column>)

14. DAX Maxx Function

It evaluates an expression for each row of a table and return the largest numeric value.

Syntax: MAXX(<table, expression>)

15. DAX Min Function

It returns the smallest numeric value in a column or between two scalar expressions.

Syntax: MIN(<column>)

16. DAX MinA Function

It returns the smallest value in a column, including any logical values and numbers represented as text.

Syntax: MINA(<column>)

17. DAX MinX Function

It returns the smallest numeric value that results from evaluating an expression for each row of a table.

Syntax: MINX(<table, expression>)

18. DAX Product Function

It returns the product of the numbers in a column.

Syntax: PRODUCT(<column>)

19. DAX ProductX Function

It returns the product of an expression evaluated for each row in a table.

Syntax: PRODUCTX(<table, expression>)

20. DAX Sum Function

It adds all the numbers in a column.

Syntax: SUM(<column>)

21. DAX SumX Function

It returns sum of an expression evaluated for each row in a table.

Syntax: SUMX(<table, expression>)

With these aggregate functions we can perform analysis on our data in PowerBI.