How to Change SUM to AVERAGE in Power BI

One of the first things you'll notice in Power BI is that when you add a numeric field to a visual, it almost always automatically calculates the sum. While summing up sales or units sold is often what you want, there are many times when you need an average instead. This article will walk you through several easy ways to change that SUM calculation to an AVERAGE, from a quick click to a more robust DAX formula.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Power BI Defaults to Summarizing Numbers

When you drag a column with numbers (like sales, quantity, or web traffic) into a Power BI report, the software looks at the data type and makes an intelligent guess about what you want to do. For numbers, the most common operation is aggregation - rolling many individual numbers up into a single summary value. Power BI's default summarization is Sum because it's the most common need for business reporting.

You want to see the total revenue, not the average. You care about the total number of products sold, not the average number. This default behavior, called implicit summarization, is designed to save you a click in most common scenarios.

But what about when the default isn't what you need? There are plenty of situations where an average tells a more meaningful story than a sum:

• Average Customer Rating: Summing up all your 1-5 star ratings would give you a meaningless, massive number. You need the average rating to understand customer satisfaction.
• Average Order Value (AOV): To know how much a typical customer spends, you need the average of all order totals, not the grand total of all revenue.
• Average Age of Customers: If you're analyzing customer demographics, you want their average age, not the sum of their ages.
• Average Session Duration: In web analytics, understanding the average time a user spends on your site is a key engagement metric.

In these cases, leaving the calculation as a sum can be misleading and unhelpful. Fortunately, changing it is straightforward.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Method 1: The Quick Click in the Visualizations Pane

This is the fastest and most common way to change the calculation for a single chart or table. You're simply telling Power BI to use a different aggregation for that specific visual.

Let's use an example. Imagine you've created a bar chart to show Sales Amount by Product Category. By default, Power BI shows the total sales for each category.

But what if you want to see the average sale price per category instead? Here's how to change it:

1. Select your visual. Click on the bar chart you want to modify to make it active.
2. Go to the Visualizations pane. On the right side of the screen, you'll see the Visualizations pane where you built the chart.
3. Find your numeric field. Look for the field you want to change, which in this case is 'Sales Amount' located in a field well (like Y-axis or Values).
4. Click the dropdown arrow. Next to the field name, you'll see a small downward-facing arrow. Click it to open a context menu.
5. Select "Average". In the menu that appears, you'll see a list of summarization options: Sum, Average, Minimum, Maximum, Count (Distinct), Count, and more. Simply select Average.

Instantly, your visual will update. Instead of showing total sales, the bar chart now displays the average sale price for each product category. This method is perfect for quick, one-off changes directly on the chart you're working on.

Method 2: Change the Default Summarization for the Column

What if you have a field that you will almost always want to see as an average? For instance, a 'Customer Satisfaction Score' column. It gets tedious to change it from 'Sum' to 'Average' hundreds of times across different visuals and report pages. This is where changing the default summarization for the entire column becomes a massive time-saver. You're setting a rule for that column so Power BI defaults to 'Average' every time you use it.

Here’s how to do it:

1. Go to the Model or Data View. You can do this by clicking the icons for 'Model view' or 'Data view' on the left-hand side of the Power BI window.
2. Select the column you want to change. In the 'Data' pane on the far right, find and click on the specific column whose default you want to update (e.g., 'Customer Satisfaction Score').
3. Open the Column Tools. Once the column is selected, a new ribbon tab called 'Column tools' will appear at the top of the window.
4. Change the Summarization property. In the 'Column tools' ribbon, look for a group called 'Properties.' You'll see a dropdown menu labeled 'Summarization.' Click it, and change it from its current setting (likely 'Sum' or 'Don't summarize') to Average.

That's it! Now, any time you drag that 'Customer Satisfaction Score' field into a new visual, Power BI will automatically calculate the average instead of the sum. This saves you repetitive work and helps ensure consistency across your entire report.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Method 3: Creating an Explicit Measure with DAX

While the first two methods are great for simple changes, they rely on Power BI's "implicit measures." As you get more advanced, it’s considered best practice to create your own "explicit measures" using DAX (Data Analysis Expressions). A DAX measure is a formula that you write and save in your data model.

Why would you do this? Measures offer far more power, control, and reusability.

• Clarity: A measure named “Average Order Value” is much clearer than a column simply named “Order Total” set to average.
• Reusability: You can use a measure in many different visuals, and if you need to update the logic, you only have to change it in one place.
• Flexibility: DAX allows you to perform calculations that are impossible with simple summarizations.

Creating a basic average measure is very simple. To do this, we'll use the AVERAGE() function in DAX.

Here are the steps:

1. Create a New Measure. In the 'Home' or 'Modeling' ribbon, click on the New Measure button. Alternatively, you can right-click the table you want the measure to belong to in the 'Data' pane and select 'New measure'.
2. Write the DAX Formula. A formula bar will appear at the top. Here, you'll type your DAX formula. The structure is: Measure Name = FUNCTION(TableName[ColumnName]). For our average sales example, the formula would be:

Average Sale Amount = AVERAGE(Sales[SaleAmount])

1. Commit the Formula. Hit Enter or click the checkmark to save the measure. Your new measure, Average Sale Amount, will now appear in your 'Data' pane with a small calculator icon next to it.

Now you can drag your Average Sale Amount measure into any visual just like a regular column. The key benefit is that this calculation is now a permanent, clearly named, and reusable part of your data model.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Advanced Method: Averaging an Expression with AVERAGEX

What if the column you want to average doesn't actually exist in your data? This is where the powerful AVERAGEX function comes in. It first performs a calculation for every single row in a table and then averages the results.

Consider a sales table that has Quantity and Unit Price columns but is missing a Total Revenue column for each line item.

If you wanted to calculate the average transaction revenue, you can't just average the 'Unit Price' column, as that ignores the quantity sold. This is a perfect problem for AVERAGEX.

Here's how you'd write a measure for this:

Average Transaction Revenue = 
AVERAGEX(
    Sales, 
    Sales[Quantity] * Sales[Unit Price]
)

Let's break that down:

• AVERAGEX(...): We're using the AVERAGEX function.
• Sales: This is the first argument. It tells DAX which table to loop through, row by row.
• Sales[Quantity] * Sales[Unit Price]: This is the second argument - the expression. For each row in the 'Sales' table, DAX calculates the quantity multiplied by the unit price.

The function effectively creates a temporary column in memory with the result of that calculation for every row, and then it computes the average of that temporary column. This allows you to perform complex averages without having to clutter your base data by adding extra calculated columns.

Final Thoughts

Whether you need a quick adjustment on a single chart or a future-proof solution for your data model, Power BI offers multiple ways to calculate an average instead of a sum. You can make an immediate change in the Visualizations Pane, set a new default for a column to save time, or write a dedicated DAX measure for ultimate control and reusability.

Learning the nuances of tools like Power BI can feel like a steep climb, especially when you start diving into DAX formulas to get the insights you need. At my company, we believe getting insights shouldn't require you to become a data analyst. With Graphed, we turn hours of report building into a simple conversation. You just connect your data sources and ask questions in plain English like, “Show me the average order value from Shopify last quarter,” and it instantly builds the charts and dashboards for you.