How to Calculate Variance in Tableau

Tracking variance is essential for understanding business performance, telling you the difference between what happened and what you expected to happen. Whether you're comparing this month's sales to last month's, or actual revenue against your forecast, calculating variance is a foundational skill for any data analyst. This article will guide you through the primary methods for calculating and visualizing variance in Tableau, helping you turn raw numbers into a clear performance story.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

What is Variance and Why Does it Matter?

In its simplest form, variance is the difference between two values. In a business context, this difference usually highlights performance against a benchmark, goal, or previous time period. Simply looking at a raw number, like "$100,000 in sales," doesn't tell you much on its own. Is that good or bad? Knowing that your target was $90,000 (+10k variance) or that last month's sales were $120,000 (-20k variance) adds crucial context.

Common examples of variance analysis include:

• Actual vs. Target Analysis: Comparing actual sales, revenue, or marketing leads against your goals or forecast.
• Period-over-Period Analysis: Comparing performance metrics from the current period (this month, this quarter) to a previous one (last month, the same quarter last year). This is often called Year-over-Year (YoY), Quarter-over-Quarter (QoQ), or Month-over-Month (MoM) analysis.
• Budget vs. Actual Spend: Tracking how your ad spend or operational costs align with the budget you set.

Calculating variance helps you quickly spot trends, identify high-performing areas, and catch potential problems before they escalate. It’s the difference between data reporting and data analysis.

Preparing Your Data for Variance Analysis

Before you jump into calculations, make sure your data is structured properly for the type of analysis you want to perform.

• For Actual vs. Target: Your data source should ideally have separate columns (or measures) for your actual values and your target values. For example, a [Sales] column and a [Sales Target] column. If your targets are in a separate spreadsheet, you may need to join or blend your data sources first.
• For Period-over-Period: All you need is a single measure (e.g., [Revenue]) and a continuous date field (e.g., [Order Date]). Tableau will use this date field to look back at previous periods.

Ensuring your data is set up correctly from the start will save you a lot of trouble down the line.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Method 1: Calculating 'Actual vs. Target' Variance Using a Calculated Field

When you have two separate measures like [Actual Sales] and [Target Sales], a standard calculated field is the most direct way to find the variance. This method is straightforward and gives you a new, reusable measure in your data pane.

Step 1: Calculate the Raw Variance

First, we'll calculate the simple numerical difference between your actual and target values.

1. In Tableau, right-click anywhere in the Data pane on the left and select Create Calculated Field.
2. Name your field something clear, like Sales Variance.
3. In the formula box, enter the simple calculation to subtract your target from your actual. It's important to use aggregate functions like SUM() to ensure the calculation works correctly at different levels of detail in your view.

SUM([Actual Sales]) - SUM([Target Sales])

Click OK. You now have a new [Sales Variance] measure you can drag into your view to see the dollar difference between your actuals and targets.

Step 2: Calculate the Percent Variance

While the raw variance is useful, percentage variance often tells a clearer story, especially when comparing items of different sizes. A $10,000 variance is massive for a product with a $20,000 target but insignificant for one with a $2,000,000 target.

1. Create another calculated field. Name this one Sales Variance %.
2. The formula for percentage variance is (Actual - Target) / Target.

(SUM([Actual Sales]) - SUM([Target Sales])) / SUM([Target Sales])

Click OK. Right-click your new [Sales Variance %] measure in the data pane, select Default Properties > Number Format..., and choose Percentage to ensure it displays correctly.

Method 2: Calculating 'Period-over-Period' Variance Using Table Calculations

When you want to compare a single measure over time (e.g., this month's sales vs. last month's), table calculations are the way to go. Table calculations operate on the data currently in your visualization, allowing you to perform calculations relative to other values in the table, like the previous row (or month).

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step 1: Set Up Your View

To start, create a simple view of your measure over time. Drag your date field to the Columns shelf (make sure it's a discrete date part like MONTH or QUARTER) and your measure (e.g., SUM([Sales])) to the Rows shelf.

Step 2: Create a 'Difference From' Quick Table Calculation

Tableau has built-in quick table calculations that make this process incredibly easy for standard comparisons.

1. On the Rows shelf, right-click the SUM([Sales]) pill.
2. Select Quick Table Calculation > Difference.

Just like that, Tableau changes the display to show the difference from the previous month. Notice that the first month is blank, as there is no prior period to compare against. You can clean this up later by either right-clicking the null indicator and selecting "Hide" or by filtering out the first period.

Step 3: Calculate Percentage Difference

To view this as a percentage, follow a similar process: right-click the SUM([Sales]) pill, but this time select Quick Table Calculation > Percent Difference. Tableau will automatically calculate the percentage change from the previous period.

Understanding the underlying formula (LOOKUP)

While quick table calculations are fast, it's helpful to know what Tableau is doing under the hood. It uses the LOOKUP() function. If you wanted to create the Year-over-Year (YoY) variance calculation manually, you'd create a calculated field like this:

SUM([Sales]) - LOOKUP(SUM([Sales]), -1)

The LOOKUP(SUM([Sales]), -1) part tells Tableau to "look up" the SUM([Sales]) value from the previous partition in the view (-1). For a percent difference, the formula would be:

(SUM([Sales]) - LOOKUP(SUM([Sales]), -1)) / LOOKUP(SUM([Sales]), -1)

Creating these manually gives you more flexibility if you need to reuse the calculation or combine it with other logic.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Visualizing Variance for Maximum Impact

Now that you have your variance calculations, the next step is to visualize them in a way that viewers can understand at a glance.

Using Color in a Text Table

One of the simplest and most effective ways to show variance is by coloring the numbers. A basic text table can be instantly enhanced with color to indicate positive or negative performance.

1. Build a text table showing your breakdown (e.g., by Product Category) along with your regular sales numbers and your variance calculation.
2. Drag your Sales Variance or Sales Variance % field onto the Color mark on the Marks card.
3. Click the Color mark and select Edit Colors. Choose a diverging palette like "Red-Green Diverging," set the center to 0, and check "Stepped Color" with 2 steps. This will make all positive numbers green and all negative numbers red.

Building a Diverging Bar Chart

A diverging bar chart is excellent for visualizing both the direction (positive or negative) and magnitude of the variance.

1. Place your dimension (e.g., Region) on the Rows shelf.
2. Place your variance calculation ([Sales Variance]) on the Columns shelf.
3. Drag another copy of [Sales Variance] onto the Color mark.
4. Click the Color mark and edit the colors as described above, using a 2-step Red-Green diverging palette centered at 0.

This creates a powerful view where bars extend to the right for positive variance and to the left for negative variance, immediately drawing attention to the biggest wins and problem areas.

Final Thoughts

Calculating and visualizing variance in Tableau transforms your raw data into a clear story of business performance. Whether you are using calculated fields for actuals vs. targets or table calculations for period-over-period comparisons, these techniques are fundamental for moving from simply seeing data to truly understanding it.

Mastering these techniques in Tableau is a valuable skill, but it often requires a lot of clicks, formulas, and careful configuration to get just right. We built Graphed to remove that friction. Instead of creating calculated fields and setting up table calculations, you can just ask a question in plain English, like, "show me the monthly variance of sales vs. our sales forecast for this year as a colored bar chart." It instantly connects to your data and builds the interactive dashboard for you, saving you valuable time so you can focus on the insights, not the setup.