AVERAGEX DAX Function in Power BI

The AVERAGEX DAX function evaluates an expression row by row over a table and returns the average (arithmetic mean) of those values.

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
)