How to Calculate Average in Power BI

Calculating an average seems straightforward, but in Power BI, what looks simple on the surface has a lot of power underneath. Depending on what you want to average and how you want to filter it, there are several ways to get the job done. This guide will walk you through the easiest methods using the UI all the way to more flexible and powerful DAX functions like AVERAGE and AVERAGEX.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

The Easiest Method: Using Aggregations in Visuals

Power BI offers a quick, code-free way to find an average directly within a visual like a Card, Table, or Matrix. This is the fastest way to get a simple average, but it’s less flexible if you have more complex needs.

Let's say you have a 'Sales' table with a 'Revenue' column and you want to see the average revenue per transaction. Here's a quick way to show it:

• Drag a Card visual onto your report canvas.
• Drag your 'Revenue' field from the Fields pane directly onto the Card visual.
• By default, Power BI will probably show the Sum of Revenue. To change this, click the small down arrow next to the 'Revenue' field in the 'Fields' section of the Visualizations pane.
• From the dropdown menu that appears, simply select Average.

That's it! The card visual will now display the average of your 'Revenue' column.

This works great for a quick overview. However, this is an implicit measure. You can't easily reference it in other calculations or use it to build more complex formulas, which is where DAX comes into play.

Creating Your First DAX Measure: The AVERAGE Function

For more control and reusability, you'll want to use DAX (Data Analysis Expressions) to create an explicit measure. The most common tool for this is the AVERAGE function. It’s simple, powerful, and easy to remember.

The syntax is straightforward:

AVERAGE(<ColumnName>)

This function takes a single argument: the column you want to average. It automatically ignores any blank cells, but it will include zeros in its calculation.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step Example: Average Sale Amount

Let’s create a reusable measure to calculate the average sale amount from a 'Sales' table.

1. Navigate to the 'Home' or 'Modeling' tab in the Power BI ribbon and click on New Measure.
2. The formula bar will appear at the top. Here, you'll name your measure and write the DAX formula.
3. Enter the following formula:
4. Press Enter to save the measure. You’ll now see your new measure (with a small calculator icon) in the Fields pane.

Now you can drag "Average Sale Amount" into any visual. The big advantage here is that this measure is dynamic. If you put it in a table that shows sales by product category, it will automatically calculate the average sale amount for each category without you having to do anything extra. This is the power of the DAX evaluation context at work.

Level Up Your Calculations: The AVERAGEX Function

The AVERAGE function is fantastic, but it has one major limitation: it can only average a single, existing column. What if you need to average a calculation that doesn't exist as a column in your tables? For instance, what if you want to find the average profit per transaction, but you only have Price and Cost columns?

This is where AVERAGEX comes in handy. AVERAGEX is an "iterator" function. This means it goes through a table row by row, performs a calculation you define for each row, and then averages the results of those calculations.

The basic syntax is:

AVERAGEX(<Table>, <Expression>)

• Table: The table the function will iterate over.
• Expression: The calculation to perform on each row of that table.

Practical Example: Calculating Average Profit

Imagine your 'Sales' table has columns named UnitPrice and UnitCost, but no 'Profit' column. You can’t use AVERAGE directly. Here’s how you'd use AVERAGEX:

• Create a New Measure.
• Enter this formula:

Here’s what Power BI does behind the scenes:

1. It looks at the Sales table you specified.
2. For the very first row, it calculates Sales[UnitPrice] - Sales[UnitCost] and temporarily stores the result.
3. It moves to the second row and does the same calculation.
4. It repeats this for every single row in the 'Sales' table (or the filtered context of your visual).
5. Once it has a list of all the profit results from every row, it calculates the average of that list.

AVERAGEX is incredibly powerful for scenarios where your averages are based on calculations that combine multiple columns.

Common Averaging Scenarios and Solutions

Business analytics is rarely just about calculating a total average. You'll often need to slice, dice, and filter your data to get meaningful insights. Here are a few common situations and the DAX patterns to solve them.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

1. Calculating an Average for a Specific Category

You may want to find the average sales amount but only for a specific category, like "Electronics". The CALCULATE function is your best friend here. CALCULATE modifies the context in which your DAX formula is evaluated.

Formula:

Average Electronics Sales = CALCULATE( AVERAGE(Sales[TotalRevenue]), 'Product'[Category] = "Electronics" )

This measure will always return the average for Electronics, regardless of what other filters are applied in your report. It forces the filter for the Electronics category to be applied.

2. Calculating an Average But Excluding Zeros

Sometimes, a zero in a dataset doesn't truly mean zero, it might represent a placeholder for missing data. Including these in your average can skew your results downwards. For example, if you're averaging customer survey scores, a '0' might mean the customer didn't answer that question.

To exclude zeros, you can use the FILTER function inside AVERAGEX.

Formula:

Average Sales (Excluding Zeros) = AVERAGEX( FILTER(Sales, Sales[TotalRevenue] > 0), Sales[TotalRevenue] )

How this works:

• The FILTER(Sales, Sales[TotalRevenue] > 0) portion first creates a temporary, virtual table containing only the rows from the 'Sales' table where TotalRevenue is greater than zero.
• AVERAGEX then iterates over this temporary table and calculates the average TotalRevenue.

3. Calculating a Weighted Average

A simple average assumes every data point has equal importance. A weighted average is needed when some data points contribute more than others. A great example is calculating the average price of products sold, weighted by the quantity sold for each transaction.

To calculate a weighted average, you can't use AVERAGE or AVERAGEX alone. You need to build it by dividing two calculations.

The logic is: (Sum of [Value * Weight]) / (Sum of [Weight])

Let's find the average price, weighted by units sold. Assume the Sales table contains UnitPrice and UnitsSold.

Formula:

Weighted Average Price = DIVIDE( SUMX(Sales, Sales[UnitPrice] * Sales[UnitsSold]), SUM(Sales[UnitsSold]), 0 )

Breakdown:

• SUMX(Sales, Sales[UnitPrice] * Sales[UnitsSold]): This iterates through the Sales table. For each row, it multiplies the UnitPrice by the UnitsSold and then sums up those results. This gives you the total revenue.
• SUM(Sales[UnitsSold]): This is a simple sum of all the units sold.
• DIVIDE(...): We use the DIVIDE function to safely handle the division. The third argument (in this case, 0) is the result if the denominator is zero, preventing errors.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Common Mistakes and Troubleshooting

My Average Value is the Same Everywhere!

If you put your measure in a table and it shows the same grand total for every row, it's usually an issue with the "filter context." The measure isn't responding to the individual rows (like product category or date). This often happens when your table relationships are not set up correctly. Make sure your Sales table is properly related to lookup tables like Product or Date.

Error: 'A single value for column cannot be determined'

This common error usually pops up when you're using a function that expects a single value, but you've provided it with a whole column without an aggregation (like SUM, AVERAGE, MIN, MAX). For example, writing Profit = Sales[UnitPrice] - Sales[UnitCost] in a measure won't work, because Power BI doesn't know which row's UnitPrice to subtract. This is exactly the problem AVERAGEX solves by iterating row by row.

Final Thoughts

You can see there are several ways to calculate an average in Power BI, ranging from a few quick clicks in the UI to writing versatile DAX measures. Using AVERAGE for direct column aggregations and AVERAGEX for row-by-row expressions will cover most of your needs, giving you the power to find the precise insights hidden in your data.

At Graphed, we believe deep analysis shouldn't require mastering complex functions or spending weeks learning tools like Power BI. That's why we built a way to do this using simple, plain English. After connecting your data sources from platforms like Salesforce, Shopify, or Google Analytics, you can skip writing formulas entirely. Just ask, "What was our average order value from Google Ads last week?" and get an instant answer or even a complete dashboard. With Graphed, you get to focus on the insights, not the learning curve.