Aggregation DAX Functions In Power BI

Power BI DAX functions provide a very essential and useful category of function called ‘Aggregation Functions’. These functions are used for creating aggregation such as SUM, AVERAGE, MIN and MAX etc. These aggregation functions are very similar to Microsoft excel functions.
 
Below is the list of commonly used aggregation function in Power BI.
 

Functions and their Description

 
AVERAGE
 
Returns the average (arithmetic mean) of all the numbers in a column.
 
Syntax- AVERAGE(ColumnName)
 
ColumnName- The column that contains the numbers for which you want the average.
 
Ex.
 
Aggregation DAX Functions In Power BI
 
AVERAGEA
 
Returns the average (arithmetic mean) of all the numbers in a column. Handles text and non-numeric values.
 
Syntax- AVERAGEA(ColumnName)
 
ColumnName- Any column that contain value for calculating average.
 
Ex.
 
Aggregation DAX Functions In Power BI
 
AVERAGEX
 
Returns the average (arithmetic mean) of a set of expressions evaluated over a table.
 
Syntax- AVERAGEX(Table, Expression)
 
Table- A table or expression that returns table over which aggregation can be performed.
 
Expression- An expression with a scalar result, which will be evaluated for each row of the table in the first argument.
 
Ex.
 
Aggregation DAX Functions In Power BI
 
COUNT
 
Counts the number in a column.
 
Syntax- COUNT(ColumnName)
 
ColumnName-Any column that contains value to be calculated.
 
Ex.
 
Aggregation DAX Functions In Power BI
 
COUNTA
 
Counts the number in a column that are not empty.
 
Syntax –COUNTA(ColumnName)
 
ColumnName- The column that contains the values to be counted
 
Ex.
 
Aggregation DAX Functions In Power BI
 
COUNTX
 
Counts the number of value which result from evaluation an expression for each row of a table.
 
Syntax- COUNTX(Table, Expression)
 
Table- The table containing the row for which the expression will be evaluated.
 
Expression- The expression to be evaluated for each row of the table.
 
Ex.
 
Aggregation DAX Functions In Power BI
 
COUNTBLANK
 
Count the number of blanks in a column.
 
Syntax- COUNTBLANK(ColumnName)
 
ColumnName-The column that contains the blank cells to be counted.
 
Ex.
 
Aggregation DAX Functions In Power BI
 
COUNTROWS
 
Counts the number of rows in a table.
 
Syntax- COUNTROWS(Table)
 
Table- The name of the table that contains the rows to be counted, or an expression that returns a table.
 
Ex.
 
Aggregation DAX Functions In Power BI
 
DISTINCTCOUNT
 
Count the number of distinct values in the column. It counts blank as a value.
 
Syntax- DISTINCTCOUNT(ColumnName)
 
CountName-Any column that contains the values to be counted
 
Ex.
 
Aggregation DAX Functions In Power BI
 
DISTINCTCOUNTBLANK
 
Counts the number of distinct value in column. It does not include BLANK value.
 
Syntax-DISTINCTCOUNTBLANK (ColumnName)
 
ColumnName-Any column that contains value to be counted.
 
Ex.
 
Aggregation DAX Functions In Power BI
 
MAX
 
Returns the largest numeric value or largest string in a column, or the larger value between two scalar expressions. Ignores logical values.
 
Syntax –MAX(ColumnNameOrScalar1,[Scalar2])
 
ColumnNameOrScalar1- Column name in which you find the largest value or any scalar value compare to scalar2.
 
Scalar2- Any scalar value compares to scalar1 or it is optional in case of column name.
 
Aggregation DAX Functions In Power BI
 
MAXA
 
Returns the largest value in a column. Doest not ignore logical values and text.
 
Syntax- MAXA(ColumnName)
 
ColumnName- Any available column of table in which you find the largest value.
 
Ex.
 
Aggregation DAX Functions In Power BI
 
MAXX
 
Returns the largest numeric value or largest string that results from evaluating an expression for each row of a table.
 
Syntax- MAXX (Table, Expression)
 
Table-The table containing the rows for which the expression will be evaluated
 
Expression-The expression to be evaluated for each row of the table.
 
Example:
 
Aggregation DAX Functions In Power BI
 
MEDIAN
 
Returns the 50th percentile of value in a column.
 
Syntax- MEDIAN(ColumnName)
 
ColumnName-The column that contains the numbers for which the median is to be computed.
 
Ex.
 
Aggregation DAX Functions In Power BI
 
MIN
 
Returns the smallest numeric value or smallest string in a column, or the smaller value between two scalar expressions. Ignores logical value.
 
Syntax- MIN(ColumnNameOrScalar1,[Scalar2])
 
ColumnNameOrScalar1- Column name in which you find the largest value or any scalar value compare to scalar2.
 
Scalar2- Any scalar value compares to scalar1 or it is optional in case of column name
 
Ex.
 
Aggregation DAX Functions In Power BI
 
MINA
 
Returns the smallest value in a column. Does not ignore logical value and text.
 
Syntax- MINA(ColumnName)
 
ColumnName- Any column in which you find the smallest number.
 
Ex.
 
Aggregation DAX Functions In Power BI
 
MINXX
 
Returns the smallest numeric value or smallest string that results from evaluating an expression for each row of a table.
 
Syntax- MINX(Table, Expression)
 
Table- The table containing the rows for which the expression will be evaluated.
 
Expression-The expression to be evaluated for each row of the table.
 
Ex.
 
Aggregation DAX Functions In Power BI
 

Summary

 
These are some of the most popular and commonly used aggregation functions of Power BI. Thanks for reading.


Similar Articles