How to Get Average of a Measure in Power BI

Trying to calculate the average of a measure in Power BI can feel surprisingly tricky. You might try wrapping your existing measure in a simple AVERAGE() function, only to be met with an error. This article will walk you through exactly why that happens and show you the right way to do it using DAX iterator functions like AVERAGEX.

Why Can't You Just 'Average' a Measure?

To understand the solution, it helps to first understand the problem, which boils down to a core Power BI concept called "context." When you write a DAX formula, Power BI evaluates it within a specific context. The two main types are Row Context and Filter Context.

• Row Context: This exists when you're working within a single row of a table. Think of a calculated column - it performs a calculation for each row, one at a time. The DAX formula is aware of the values in that specific row.

• Filter Context: This is the set of active filters being applied to your data model. When you click on a bar in a chart or a slice in a slicer, you're changing the filter context. Measures are calculated within the current filter context.

The standard AVERAGE() function is designed to work on a column, not on the result of another aggregation. When you create a measure like Total Sales = SUM(Sales[Revenue]), this measure doesn't have a pre-calculated value sitting in a column. It's a dynamic calculation that produces a single value based on whatever filter context is active (e.g., for a specific day, product category, or the entire dataset).

Because your [Total Sales] measure returns a single number, not a column of numbers, wrapping it in AVERAGE([Total Sales]) doesn't work. The AVERAGE function has no column to average. You need a way to first generate a temporary table of your measure's results, and then calculate the average of that table. This is exactly what iterator functions are for.

The Solution: Meet the DAX "X" Functions

DAX includes a powerful family of functions known as "iterators," which you can almost always identify by the "X" at the end of their name: SUMX, COUNTX, MINX, MAXX, and, for our purposes, AVERAGEX.

Iterator functions work in two steps:

1. They iterate, row-by-row, over a specified table.

2. For each row, they evaluate an expression or measure that you provide.

3. Finally, once all the rows have been evaluated, they perform an aggregation (like sum, count, or average) on the list of results they generated.

This "evaluate then aggregate" process is precisely what we need to calculate the average of a measure.

Step-by-Step: Calculating Average Daily Sales

Let's walk through a common business scenario: you have a measure for total sales, and now you want to find your average daily sales for a given period.

Step 1: Create Your Base Measure

First, make sure you have a basic measure for the value you want to average. For this example, we'll create a Total Sales measure. This measure will calculate the sum of the 'Revenue' column from our 'Sales' table.

In the DAX formula bar, enter:

Step 2: Define Your Goal More Clearly

When we say "average of a measure," what we really mean is, "the average result of this measure across a specific dimension." For this example, our goal is to find the "average of [Total Sales] per Day."

This means we need to:

• Make a list of all individual days we want to consider.

• Calculate our [Total Sales] measure for each of those days.

• Calculate the average of all those daily sales results.

This is the logical flow that AVERAGEX will handle for us in a single formula.

Step 3: Create the Average Measure with AVERAGEX

Now, let's create our new measure. This formula will iterate over a list of unique dates and calculate the [Total Sales] for each one, then average the results.

In the DAX formula bar, enter:

That's it! This new Average Daily Sales measure will now correctly compute the average you need.

Step 4: Breaking Down the Formula

Let's dissect that DAX formula to fully understand what Power BI is doing.

AVERAGEX(table, expression)

• AVERAGEX: As we discussed, this is our iterator function. It tells Power BI to go through a table, perform a calculation for each row, and then average the final list of results.

• VALUES('Calendar'[Date]): This is the first part of the formula and defines the table that AVERAGEX will iterate over. The VALUES function returns a single column, de-duplicated table. In this case, it gives us a virtual table containing a unique list of all dates within the current filter context. This sets the granularity of our calculation. We are telling the function, "I want you to repeat the calculation for each unique 'Date'."

• [Total Sales]: This is the second argument, the expression that AVERAGEX will evaluate for each row of the virtual table we created in the first step. So, for each date in our unique list of dates, Power BI will calculate [Total Sales].

Step 5: Putting it to Use

You can now use your [Average Daily Sales] measure in any visual. A Card visual is a great way to see the overall average. If you place it in a table grouped by Month or Year, you will see the average daily sales calculated specifically for that period, all thanks to the magic of filter context.

For example, if you create a table visual with 'Year' and 'Month' from your Calendar table and add both [Total Sales] and [Average Daily Sales], you'll see something like this:

As you can see, the measure dynamically calculates the correct daily average for each month.

Common Variations: Adapting the Pattern

Once you understand the AVERAGEX(VALUES(...), [Measure]) pattern, you can easily adapt it for different scenarios just by changing the column within the VALUES function.

Average Sales per Product Category

Want to see the average sales value across your product categories? Just switch the column in your VALUES function to the dimension you want to average over.

Average Monthly Sales

To get the average total sales for each month, you would set the granularity to month.

A Few Common Pitfalls to Avoid

• Forgetting About Blanks: Be aware that AVERAGEX, like AVERAGE, ignores blank values in its final calculation. This is usually the desired behavior (e.g., you don't want days with no sales to drag your average down), but it's important to be aware of.

• The Importance of a Date Table: While you could use VALUES('Sales'[OrderDate]), it's a best practice in Power BI to always use a dedicated Calendar or Date table. This ensures your time intelligence calculations are accurate and accounts for every single day in a period, even those without sales.

• Forgetting Filter Context: Remember your iterator function is still evaluated within the current filter context. If a user selects "USA" in a slicer, your Average Daily Sales measure will correctly recalculate to show the average daily sales for just the USA.

Final Thoughts

Calculating the average of a measure in Power BI comes down to understanding context and using the right tools for the job. By pairing an iterator function like AVERAGEX with VALUES, you can define the specific granularity for your average, turning a seemingly complex problem into a clean and repeatable DAX pattern.

Learning DAX patterns is a necessary part of mastering tools like Power BI, but it’s often a major source of friction for teams who just want quick answers from their data. This is where we wanted to create a simpler way. With Graphed, you simply connect your data sources - like Shopify, Google Analytics, or Salesforce - and then ask your questions in plain English. Instead of learning AVERAGEX and debugging context issues, you can just ask, "What were our average daily sales last month?" and get an interactive chart in seconds, giving you back time to focus on strategy instead of formulas.