AVERAGEX DAX Function in Power BI

The AVERAGEX function in Data Analysis Expressions (DAX) is a powerful iterator designed to calculate the arithmetic mean of an expression evaluated for each row of a table. Unlike the standard AVERAGE function, which performs a simple column-level aggregation, AVERAGEX allows you to perform complex row-level calculations (such as multiplying price by quantity or applying tax rates) before the final average is computed. This makes it an indispensable tool for scenarios involving weighted averages, dynamic filtering, and multi-category performance analysis where a simple column average would be mathematically incorrect.

DAX Syntax AVERAGEX(table, expression)

The function has the following parameters:

• table: It specifies the name of a table, or an expression over which the 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.

Example: Basic AVERAGEX with a calculated value.

DAX

AVERAGEX Measure =
AVERAGEX(
    MarksTable,           -- Table to iterate over
    MarksTable[Marks]     -- Expression to evaluate and average
)      

This is like AVERAGE(MarksTable[Marks]). The output of the above code is shown below:

Example: Average Marks per Subject Category.

To calculate the average of total marks, but per Subject category.

DAX

AVERAGEX Marks per Subject =
AVERAGEX(
    VALUES(MarksTable[Subject]),       -- Get a unique list of subjects
    CALCULATE(SUM(MarksTable[Marks]))  -- Calculate total marks for each subject
)    

The output of the above code is shown in the image below:

Explanation:

• VALUES(MarksTable[Subject]): Returns a list of distinct subjects. The VALUES function respects the current filter context.
• For each subject, it calculates total marks and then averages those totals using AVERAGEX.

Goal of this DAX: Find the average of total marks per subject.

1. Get all unique subjects.
2. For each subject, calculate the total marks scored.
3. Average those subject totals.

Step-by-Step Backend Breakdown:

Step 1: VALUES(MarksTable[Subject])

• Creates a virtual table with distinct subjects.
• Example result:
  • Science_Marks
  • Math_Marks
• AVERAGEX will now iterate row-by-row over this list.

Step 2: AVERAGEX Iterator Starts

• Loops through the virtual table.
• For each subject, it calculates: CALCULATE(SUM(MarksTable[Marks]))

Step 3: CALCULATE(...) Converts Row Context → Filter Context

• First iteration: Subject = "Science_Marks"
  • Applies filter where Subject = Science_Marks.
  • SUM of Marks = 365
• Second iteration: Subject = "Math_Marks"
  • Applies filter where Subject = Math_Marks.
  • SUM of Marks = 488

Step 4: AVERAGEX Averages These Totals

• Total for Science_Marks = 365
• Total for Math_Marks = 488
• Average = (365 + 488) / 2 = 426.5

Behind the Scenes: Row Context vs Filter Context

• Row context: Happens while iterating over rows (like inside AVERAGEX).
• Filter context: Filters applied to the model (via visuals or CALCULATE).
• CALCULATE bridges the gap by converting row context into filter context so that SUM only considers rows for the current subject.

Alternative with Measure:

Instead of using CALCULATE, create a measure (measures inherently work in filter context):

DAX

The above measure can be used in our calculation.

DAX

AVERAGEX Marks per Subject =
AVERAGEX(
    VALUES(MarksTable[Subject]),       -- Get a unique list of subjects
    [Total Marks Measure]  -- Calculate total marks for each subject
) 

AVERAGE vs. AVERAGEX

Choosing the right function depends on your data structure and calculation needs:

• AVERAGE: Best for simple arithmetic means of a single column. It works only in filter context.
• AVERAGEX: Required for row-level math involving multiple columns or iterating over filtered/virtual tables. It introduces row context during iteration.