Creating Measures Using AggregateX Functions
There are many AggregateX functions for creating DAX measures in Power BI. These functions are used to aggregate values over a table or table expression by performing row-wise calculations. Below are the AggregateX functions available:
SUMX, AVERAGEX, COUNTX, MINX and MAXX
Syntax:
AggregateX Function (Table, Expression)
The AggregateX function will have two arguments, where first is a table name and the second is an expression or calculation on the table columns.
Example:
AggregateX Demo = SUMX('Global-Superstore','Global-Superstore'[Shipping Cost] - 'Global-Superstore'[Sales])
Below is the list of common Aggregate DAX functions with description:
Function Name |
Description of each Function |
|---|---|
|
AVERAGE |
Average function returns the average value (arithmetic mean) of all the numbers in the specified column. |
|
AVERAGEA |
AverageA function returns the average value (arithmetic mean) of the values in a column. |
|
AVERAGEX |
AverageX function calculates the average value (arithmetic mean) of a set of expressions evaluated over a table. |
|
COUNT |
The Count function is used to get the total number of rows in the specified column that contain non-blank values. |
|
COUNTA |
The CountA function counts the total number of rows in the specified column that contain non-blank values. |
|
COUNTX |
This function counts the number of rows that contain a number or an expression that evaluates to a number, when evaluating an expression over a table. |
|
COUNTBLANK |
The Countblank function counts the total number of blank cells in a column. |
|
COUNTROWS |
The CountRows function counts the total number of rows in the specified table, or in a table defined by an expression. |
|
DISTINCTCOUNT |
The DistinctCount function counts the total number of distinct values in a specified column. |
|
MAX |
The MAX function returns the largest numeric value in a column, or highest value between two scalar expressions. |
|
MAXA |
The MAXA function returns the largest value in a table column. |
|
MAXX |
The MAXX function evaluates an expression for each row of a table and returns the largest numeric value |
|
MIN |
This function returns the smallest numeric value in a column, or smallest value between two scalar expressions. |
|
MINA |
This returns the smallest value in a column, including any logical values and numbers represented as text. |
|
MINX |
The MINX function returns the smallest numeric value that results from evaluating an expression for each row of a table. |
|
SUM |
The SUM function is used to add all the numeric values in a column. |
|
SUMX |
The sum of an expression that is evaluated for every row in a table is returned by the SUMX function. |
AggregateX Functions are Iterator Functions
The AggregateX Functions like SUMX, AVERAGEX, COUNTX, MINX and MAXX are also called iterator functions. They are useful when we need to make calculations using columns from different related tables. AggregateX Functions are called iterator functions since they evaluate the expression over each row of a table specified.
Below is an example for Iteration using AggregateX function:
SumOfCookiesabove800 = SUMX('Order Data',IF('Order Data'[Cookies Shipped] > 800,'Order Data'[Cookies Shipped],0))
Creating measures using DAX
DAX (Data Analysis Expressions) Measures in Power BI are standard aggregation functions or dynamic custom expressions defined with the DAX formula language. Using this formula language, you can calculate, combine, and filter data that can return one or more values. DAX Measures are dynamic and user-defined formulas where results change depending on context. This article discusses the DAX Measures in Power BI, how to create and edit, and how to use them in Power BI Visualization reports.