How to Change Sum to Average in Tableau

One of the first things you'll notice when dragging a number onto a Tableau worksheet is that it almost always shows up as a sum. While summing up sales or revenue is useful, it’s not always the metric you need. Often, you're looking for the average to understand typical performance, not the total. This guide will walk you through several easy methods to change the SUM aggregation to an Average in Tableau, from quick, on-the-fly adjustments to setting a permanent default.

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 Tableau Defaults to SUM, Anyway?

Before jumping into the "how," it helps to quickly understand the "why." It's not a random choice by Tableau, it's based on how the software categorizes your data. When you connect a data source, Tableau scans the columns and classifies them as either Dimensions or Measures.

• Dimensions: These are categorical fields. Think of things like dates, customer names, regions, or product categories. They are used to slice, dice, and provide context to your data. When you drag a dimension into the view, Tableau creates labels.
• Measures: These are numerical fields that you can perform mathematical calculations on. Things like Sales, Profit, Quantity, or Site Visits fall into this bucket.

When you drag a measure into your view (like onto the 'Rows' or 'Columns' shelf), Tableau needs to aggregate it. Aggregation means taking many individual values and consolidating them into a single value. SUM is simply the most common and logical default for most business metrics. Total revenue is asked for far more often than average row-level revenue. Luckily, changing this is incredibly simple.

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 Method on the Fly

This is the most common and fastest way to change aggregation for a specific chart you're building. You aren’t changing the metric permanently, just for its use in a particular worksheet. This is perfect for quick exploration and one-off visualizations.

Let's say you've dragged the Sales measure onto the Rows shelf and Category onto the Columns shelf. Right now, it shows the total sales for each category.

Here's how to change it to show the average sale per transaction for each category.

1. Find the green pill representing your measure (e.g., SUM(Sales)) on the Rows, Columns, or Marks card.
2. Right-click on the pill. A context menu will pop up.
3. Hover over the Measure option in the menu. You'll see a list of available aggregations like Sum, Average, Median, Count, and more.
4. Select Average from the list.

That's it! The pill will change from SUM(Sales) to AVG(Sales), and your visualization will instantly update to show the average instead of the total.

This is your go-to method for everyday analysis when you need to switch perspectives quickly without making any permanent changes to your data source.

Method 2: Changing the Default Aggregation

Sometimes you’ll have a measure that you almost always want to view as an average. For instance, metrics like 'Customer Satisfaction Score', 'Session Duration', or 'Temperature' are more commonly analyzed as averages rather than sums. Changing the default aggregation for that measure can save you a lot of clicks over time.

By changing the default property, you're telling Tableau, "From now on, whenever I use this field, please start with an average."

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

How to Set a New Default Aggregation:

1. Navigate to the Data pane on the left side of your screen (where your Dimensions and Measures are listed).
2. Right-click on the measure you want to change (e.g., Sales).
3. From the context menu, go to Default Properties.
4. In the sub-menu, select Aggregation.
5. Choose Average from the list of aggregation types.

Now, whenever you drag that Sales field into any new view or worksheet, it will automatically appear as AVG(Sales) instead of SUM(Sales). This keeps your analysis consistent and saves you from having to repeat Method 1 every single time. You can still change it to a SUM or another aggregation on a per-worksheet basis if you need to, but the starting point will be your new preferred default.

Method 3: Creating a Calculated Field for Averages

Creating a calculated field is the most flexible and powerful method. It is highly recommended when you need to use both the total and the average amount of a field frequently throughout your workbook. It also gives you more control over the exact calculation you're performing.

Instead of changing the original Sales field, you create a new, separate measure called something like "Average Sales."

Steps for Creating an Average Calculation:

1. In the Data pane, right-click anywhere in the empty space and select Create Calculated Field. (Alternatively, you can go to the 'Analysis' menu at the top and select 'Create Calculated Field').
2. A dialog box will appear. First, give your new field a descriptive name. For example, "Average Sale Amount".
3. In the formula box, type the following simple expression:
4. Make sure to replace [Sales] with the actual name of your measure. Tableau will help you with autocomplete.
5. Click OK.

You will now see "Average Sale Amount" listed as a new measure in your Data pane. You can drag and drop this field just like any other measure, and it will always calculate the average. Your original Sales measure remains unchanged, still defaulting to SUM.

Why is This Method So Useful?

• Clarity: It creates an explicitly named field for your average calculation, making your analysis easier for others (and your future self) to understand.
• Flexibility: You can now easily build a dashboard that shows SUM(Sales) right next to your Average Sale Amount without having to change aggregations back and forth.
• Foundation for More Complexity: This method is the gateway to more advanced calculations. For example, you might want to calculate the Average Order Value (AOV), which could be SUM([Sales]) / COUNTD([Order ID]). This is impossible to do with the simple right-click method but straightforward with a calculated field.

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

A Quick Note on Granularity

It's important to remember that the average calculation is dependent on the level of detail (or granularity) of your view. Tableau performs the AVG() calculation on the rows in your underlying data source for the dimensions present in your chart.

• AVG(Sales) with the State dimension will show you the average sale amount for each state.
• AVG(Sales) with the Customer Name dimension will show you the average amount each customer spent per transaction.

If you remove all dimensions from the view, AVG(Sales) will give you the single average sale amount across your entire dataset. As you add or remove dimensions, Tableau recalculates the average based on the new context. Understanding this concept is essential for accurate data analysis and ensuring your charts are telling the right story.

Final Thoughts

Switching between sum and average is a fundamental skill in Tableau, and mastering these three methods will make your data exploration faster and more intuitive. Whether you need a quick, one-time change with a right-click, a permanent adjustment with default properties, or a flexible, reusable measure via a calculated field, you now have the tools to analyze your data the way you need to.

While mastering tools like Tableau is empowering, we know that sometimes you need answers without getting stuck in menus and configuration. That's why we built Graphed. Instead of setting up calculations, you can just ask in plain English: "What was our average sale price per region last quarter?" and get a report instantly. We automate the entire process, from connecting your data to creating the dashboards you need, so you can focus on insights, not setup.