AVERAGEX - DAX Calculation
Microsoft Documentation

AVERAGEX( ) | DAX Function

DAX (Data Analysis Expressions) is a formula language used in Power BI and other Microsoft tools to perform data modeling and calculations.

The AVERAGEX() function in DAX is used to calculate an average of a column of data, filtered by one or more conditions.

Table of Contents

AVERAGEX ( )

Calculates the average (arithmetic mean) of a set of expressions evaluated over a <table>

  • The AVERAGEX( ) function enables you to evaluate expressions for each row of a table, and

    • Then take the resulting set of values and calculate its arithmetic mean.
      • Therefore, the function takes a <table> as its first argument,
        • And an <expression> as the second argument.

  • AVERAGEX( ) follows the same rules as AVERAGE( )

  • You CANNOT include:

    • Non-numeric cells
    • Null cells

  • <table> and <expression> arguments are required.

  • When there are no rows to aggregate, the function returns a blank.

    • When there are rows, but none of them meet the specified criteria, then the function returns 0.

  • This function is NOT SUPPORTED for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Formula Syntax

The general structure and syntax for the AVERAGEX() function:

AVERAGEX ( ) Example Syntax

= AVERAGEX(table, expression,filter)
Accepted Formula Arguments

table

  • is the table containing the data you want to average,
    expression
  • is the column you want to average, and "filter" is an optional parameter that allows you to filter the data before averaging.

AVERAGEX ( ) with FILTER( )

First, we will provide a Quick Explanation of FILTER() function, then we will review the important concept of "Context Transition", and finally review the usage of the AVERAGEX() function in conjunction with the FILTER()

Filter( ): Syntax & Explanation

See commentary in the code snippet below
comments and notes can be found after the "//" symbol the end of the each line

FILTER ( ) Syntax

= FILTER ( 
	<Table>,                     //  The table to be filtered.
		<FilterExpression> )     //  A True/False (boolean) expression that is to be evaluated for each row of the table.
Values Returned:

The FILTER() function returns a [TABLE].

  • This resulting table can be either the same, virtual, or other specified table,
    • which the filter criteria has been applied
  • Containing only the filtered rows that meet the criteria
Considerations: Context Transition
Further Documentation | CONTEXT TRANSITION
Be Careful:

Nested FILTER( ) in AVERAGEX( )

Usage:
Pro tip

Confirm Relationships when Evaluating the average of an it that is dependent on an attribute from a separate table

  • To use AVERAGEX() when calculating averages across multiple fields and utilizing a date table that has a one-to-many relationship with a transaction table containing a transaction date, you will first need to create a relationship between the date table and the transaction table on the transaction date field.

Then you can use the AVERAGEX() function in a calculated column or measure, along with the FILTER() function to filter the data by the conditions you want to use.

Example

To calculate the average transaction amount for a specific date and product, you can use the following DAX formula:

AVERAGEX ( ) with nested FILTER

= AVERAGEX (                                           // ------------------------- COMMENTS ---------------------------------------
    FILTER (
        Transactions,                                  // replace "Transactions" with The table name of your choice
	        Transactions[Product] = "Product A"        // replace with the [Column Name] from your specified table
            &&                                         // "&&" is equivelant to "AND" and needed if two "Columns" are being evaluated
            Transactions[TransactionDate] = 2022-01-01 // replace with the table and column of 2nd criteria you would like to filter by 
    ),                                                 // -->  ")," <-- this "ends" the FILTER evaluation
    Transactions[TransactionAmount]                    // replace with the "table" and "column" that you would like to find the average   
)              


//
// ----------------------------------------- NOTE | EVALUATION ORDER --------------------------------------------------------
// The Table you selected is first "filtered" to return with only the rows which meet both of the conditions (in this example)
// After the table is "filtered" THEN the "average" of the column you identified is calculated
Values Returned:

The above formula will calculate the average of the TransactionAmount column within the Transactions Table

  • Filtered by rows where the:
    • "Product" Column is equal to "`Product A`"
      AND
    • "TransactionDate" Column is equal to "2022-01-01".

Example: AVERAGEX( ) with FILTER( ) and RELATED( )

It's important to note that, if the date table has a many-to-one relationship with the transaction table, you can use the RELATED() function to grab the date value,

  • Then, simply "filter" the transaction table using the date value as the filter.

EXAMPLE with FILTER( ) and RELATED ( )

AVERAGEX (                                       
    FILTER (
        Transactions,                                                            
	        Transactions[Product] = "Product A"  
            &&                                   
            Transactions[TransactionDate] =       
	            RELATED( Date[Date] )            
    ),                                           
    Transactions[TransactionAmount]

This will return the same result as the first example but it's utilizing the relationship between the date and transaction table.