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.
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.
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.
COUNT
Counts the number in a column.
Syntax- COUNT(ColumnName)
ColumnName-Any column that contains value to be calculated.
Ex.
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.
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.
COUNTBLANK
Count the number of blanks in a column.
Syntax- COUNTBLANK(ColumnName)
ColumnName-The column that contains the blank cells to be counted.
Ex.
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.
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.
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.
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.
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.
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:
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.
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.
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.
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.
Summary
These are some of the most popular and commonly used aggregation functions of Power BI. Thanks for reading.
Join the conversation! Your thoughts help the community grow.