Understanding AVERAGE, AVERAGEX and AVERAGEA in DAX

What Are Average Operations in DAX?

An average represents the arithmetic mean of a set of values.
DAX provides multiple functions for calculating averages because the calculation
can be performed directly on a column or by evaluating an expression row by row.

In this example, the CountTable table contains three columns:
Column A, Column B, and Column C.
We will use the same table to understand how the three functions behave.

1. Displaying the CountTable

Query

EVALUATE
    CountTable

Step-by-step explanation

Step 1: EVALUATE
EVALUATE tells DAX to return a table expression as the query result.

Step 2: CountTable
CountTable is the table expression. DAX returns the rows and columns
available in this table.

Why do we display the table first?

Looking at the source data helps us understand what values each average function
will process. This becomes especially important when the data contains
blank values or text values.

2. Using AVERAGE

Query

EVALUATE
    ROW(
        "Average", AVERAGE(CountTable[Column A]),
        "Average X", AVERAGEX(CountTable, COALESCE([Column B], 0)),
        "Average A", AVERAGEA(CountTable[Column C])
    )

The first calculation in the ROW function is:

AVERAGE(CountTable[Column A])

Step-by-step explanation

Step 1: CountTable[Column A]
We provide the Column A column as the input to the
AVERAGE function.

Step 2: AVERAGE
AVERAGE calculates the arithmetic mean of the numeric values
in the specified column.

Conceptually, the calculation is the total of the values divided by the number
of values considered in the average.

3. Using AVERAGEX

The second calculation uses AVERAGEX:

AVERAGEX(CountTable, COALESCE([Column B], 0))

Step-by-step explanation

Step 1: CountTable
The first argument is CountTable. This tells DAX which table
should be iterated.

Step 2: Row-by-row evaluation
AVERAGEX evaluates the expression for each row of the supplied table.
This is the important difference between AVERAGE and
AVERAGEX.

Step 3: COALESCE([Column B], 0)
For every row, DAX evaluates [Column B]. If the value is blank,
COALESCE replaces that blank with 0.

Therefore, the expression being averaged is not simply the original
Column B values. It is the row-by-row result of:

COALESCE([Column B], 0)

Why use AVERAGEX instead of AVERAGE?

AVERAGEX becomes useful when the value to be averaged is not simply
an existing column, but must first be calculated.

For example, you could calculate an average of a row-level expression such as:

AVERAGEX(
    Sales,
    Sales[Sales Amount] - Sales[Total Product Cost]
)

Here, DAX calculates the expression for each row and then averages those results.

4. Using AVERAGEA

The third calculation uses AVERAGEA:

AVERAGEA(CountTable[Column C])

Step-by-step explanation

Step 1: CountTable[Column C]
We provide Column C as the input column.

Step 2: AVERAGEA
AVERAGEA is designed to calculate an average while applying
special handling to logical and text values.

This makes AVERAGEA different from the regular
AVERAGE function. When working with mixed data types, it is
important to understand how those values are treated before using this function.

5. Understanding the Complete ROW Query

Now let’s look at the complete query:

EVALUATE
    ROW(
        "Average", AVERAGE(CountTable[Column A]),
        "Average X", AVERAGEX(CountTable, COALESCE([Column B], 0)),
        "Average A", AVERAGEA(CountTable[Column C])
    )

Step-by-step explanation

Step 1: EVALUATE
The query begins with EVALUATE, which returns the table expression
produced by ROW.

Step 2: ROW
ROW creates a single-row table containing the calculated values.

Step 3: “Average”
This is the output column name for the result of
AVERAGE(CountTable[Column A]).

Step 4: “Average X”
This is the output column name for the result of
AVERAGEX(CountTable, COALESCE([Column B], 0)).

Step 5: “Average A”
This is the output column name for the result of
AVERAGEA(CountTable[Column C]).

What will you get?

The exact numerical results depend on the values stored in your
CountTable.

6. AVERAGE vs AVERAGEX vs AVERAGEA

7. The Most Important Difference: Column vs Expression

One of the easiest ways to remember the difference is to focus on what you are
averaging.

8. Understanding COALESCE in the AVERAGEX Example

Our query uses:

COALESCE([Column B], 0)

COALESCE returns the first value that is not blank. In this example,
if [Column B] contains a value, that value is returned. If it is
blank, 0 is returned.

This means the AVERAGEX calculation explicitly treats blank
values as zero in the expression being averaged.

EVALUATE
    ROW(
        "Average", AVERAGE(CountTable[Column A]),
        "Average X", AVERAGEX(CountTable, COALESCE([Column B], 0)),
        "Average A", AVERAGEA(CountTable[Column C])
    )

📥 Practice Data:
Download Practice Data Free

🎥 Prefer learning through video?
DAX YouTube Playlist