How to Calculate Average in Tableau

Calculating an average is one of the most fundamental operations in data analysis, and Tableau makes it incredibly simple once you know where to look. Whether you're a new user or looking to perform more complex comparisons, understanding the different ways to find an average will unlock deeper insights into your data. This article will walk you through several methods, from basic drag-and-drop actions to powerful calculated fields and Level of Detail expressions.

The Quickest Way: Changing the Aggregation

Tableau is designed to aggregate your data automatically. When you drag a measure (any field with numerical data, like Sales or Profit) into your view, Tableau will usually apply a SUM() function to it by default. The fastest way to calculate an average is to just change that default aggregation.

Let's use a common example. Imagine you have sales data and you want to find the average sale amount instead of the total.

1. Connect to your data source (we'll use the Sample - Superstore dataset included with Tableau for this example).
2. Drag the Sales measure onto the Rows shelf. You'll see Tableau create a vertical bar chart, and the pill on the Rows shelf will say SUM(Sales).
3. Now, right-click on the SUM(Sales) pill.
4. In the context menu, hover over Measure. You'll see a list of available aggregations like Sum, Average, Median, Count, etc.
5. Click on Average.

That's it! The pill will now read AVG(Sales) and your visualization will update to show the single average sales value from your entire dataset. You can now slice this average by different dimensions. For instance, if you drag the Region dimension to the Columns shelf, you'll see four bars, each representing the average sale amount for that specific region.

This method is perfect for quick, high-level analysis where you need a straightforward average of a measure in your view.

Gaining Control with Calculated Fields

While changing the aggregation is fast, you'll often need more flexibility. Calculated fields allow you to create new fields (columns) in your data source based on functions and logic you define. This is where you can start asking more specific questions of your data.

Creating a Simple Average Calculation

You can create a dedicated calculated field that always computes the average. At first, this might seem like more work than the first method, but it's incredibly useful for building more complex dashboards where you might want to reference the average sale price in multiple calculations.

1. In the top menu, go to Analysis > Create Calculated Field.
2. Give your calculated field a clear name, like "Average Sales Per Order."
3. In the formula editor, type the following: AVG([Sales])
4. Click OK.

You now have a new field in the Measures pane called "Average Sales Per Order." When you drag this calculated field into your view, it will automatically compute the average of sales based on the dimensions present in your visualization. The real power here is that you can now combine it with other fields. For example, you could create another calculation called "Average Profit Ratio" using the formula: AVG([Profit]) / [Average Sales Per Order].

Creating a Conditional Average

What if you want to find the average of a specific slice of your data? For example, what's the average sale amount for just the "Technology" category?

1. Create a new calculated field and name it "Average Tech Sales."
2. Enter this formula: AVG(IF [Category] = 'Technology' THEN [Sales] END)
3. Click OK.

This formula first uses an IF statement to check which rows belong to the 'Technology' category. For those rows, it returns the 'Sales' value, for all other rows, it returns NULL. The AVG() function then cleverly ignores all the NULL values and only calculates the average for the Technology sales. Now you have a single, reusable field that you can drop into any visualization to see your average tech sales.

Advanced Power: Level of Detail (LOD) Expressions

Level of Detail, or LOD, expressions are one of Tableau's most powerful features. They allow you to compute an aggregation at a different level of detail than what is currently shown in your view. This is essential when you need to compare a value against a group average.

Imagine your view shows Sales by Sub-Category, but you want to find the average sale amount per customer within each of those sub-categories. The view’s level of detail is "Sub-Category," but the average you need must first be calculated at the "Customer" level.

Using a FIXED LOD for a Customer-Level Average

The FIXED LOD expression allows you to compute a value for specified dimensions, regardless of what other dimensions are in the view.

1. First, we need to calculate the total sales for each individual customer. Create a calculated field named "Total Sales per Customer."
2. Use this FIXED LOD formula: { FIXED [Customer Name] : SUM([Sales]) } This tells Tableau: "For every unique Customer Name, calculate the total sum of their sales." This calculation runs across your entire dataset and creates a temporary table in the background.
3. Click OK.
4. Now, drag Sub-Category to the Rows shelf.
5. Next, drag your new calculated field, "Total Sales per Customer," onto the Text mark on the Marks card.
6. By default, it will show up as SUM(Total Sales per Customer). Right-click this pill, go to Measure, and change it to Average.

You will now see a table that lists each sub-category along with the average amount spent by customers who purchased items from that category. This is an incredible insight that would be very difficult to get without LODs.

Calculating a Grand Total Average

Another common use for LODs is to calculate an overall average that doesn't change when you apply filters. You can use this to compare a specific dimension against the total average. For example, you could display a bar for each Region's average sales and a reference line showing the average sales across all regions.

1. Create a calculated field named "Overall Average Sales."
2. Use this LOD formula, which has no dimension specified: { FIXED : AVG([Sales]) } Because we didn't specify a dimension after FIXED, this calculation finds the average of Sales across the entire dataset.
3. You can now drag any dimension, like State, to columns and your regular AVG(Sales) to rows. Then, drag "Overall Average Sales" onto Detail in the Marks card and add it as a reference line to your chart.

Now you have a dynamic chart where you can easily see which states are performing above or below the national average.

Averages Over Time: Using Table Calculations

Table calculations are unique because they compute on the data currently visible in your visualization. They are perfect for calculations that depend on the relative position of a data point, such as calculating a moving average over time.

A moving average smooths out short-term fluctuations and helps identify longer-term trends. Let's create a 3-month moving average of sales.

1. Place Order Date on the Columns shelf (make sure it's set to Month) and SUM(Sales) on the Rows shelf. This will give you a line chart of sales over time.
2. Right-click the SUM(Sales) pill on the Rows shelf.
3. Select Add Table Calculation.
4. A dialogue box will appear. For Calculation Type, select Moving Calculation.
5. Under Summarize values using, choose Average.
6. For the window, set it to compute over the "Previous 2" values, and check the box for "Current value." This defines a 3-point window (the two previous months plus the current month).
7. Close the dialogue box.

Your line chart will now show a smoothed-out trend line representing the 3-month moving average of your sales data. This is an incredibly useful technique for time-series analysis right inside Tableau.

Final Thoughts

From a simple right-click to complex Level of Detail expressions, Tableau provides a full toolkit for calculating averages. By mastering the quick aggregation switch, calculated fields for conditional logic, LODs for multi-level analysis, and table calculations for trends, you can go beyond basic reporting and start uncovering the real stories hidden in your numbers.

As you connect more diverse data sources - whether it's transactional data from Shopify, web traffic from Google Analytics, or CRM data from Salesforce - automating this kind of analysis is the next step. At Graphed you handle this complexity for you. By centralizing your data, we let you build dashboards and ask analytical questions using natural language. No need to remember if it’s a LOD or a table calculation, simply ask "what was our average order value from Facebook Ad campaigns last quarter?" and we instantly build the report, saving you time for what matters most: making strategy decisions.