Running total in Tableau - Deep Dive

Overview - Running total

What is it?

A running total is a way to add up numbers step-by-step across a sequence, like days or months. It shows the total amount so far, not just the current value. In Tableau, it helps track progress over time or cumulative sums easily. This makes trends and growth clearer in reports.

Why it matters

Without running totals, you only see isolated numbers, missing the bigger picture of how values build up. Running totals help businesses understand growth, sales accumulation, or resource usage over time. This insight supports better decisions, like spotting when sales accelerate or slow down.

Where it fits

Before learning running totals, you should understand basic Tableau charts and how to use measures and dimensions. After mastering running totals, you can explore more advanced table calculations, moving averages, and time series analysis.

Mental Model

Core Idea

A running total adds each new value to the sum of all previous values in a sequence, showing cumulative progress.

Think of it like...

Imagine filling a jar with coins every day. Each day you add coins, and the jar’s total grows. The running total is like counting all coins in the jar up to today, not just today’s coins.

Sequence:  1   2   3   4   5
Values:    5   3   7   2   4
Running:   5  (5+3)=8 (8+7)=15 (15+2)=17 (17+4)=21

Build-Up - 7 Steps

1

Foundation

Understanding basic totals

Concept: Learn what a total sum means in data and how it differs from individual values.

A total sum adds all values in a group to get one number. For example, total sales in a month add all daily sales. This is different from daily sales, which show one day at a time.

Result

You can see the total amount for a whole period, like total sales for January.

Understanding totals is key because running totals build on this idea by adding values step-by-step instead of all at once.

2

Foundation

Introducing cumulative addition

Concept: Learn how adding values one after another creates a running total.

Instead of adding all values at once, cumulative addition adds each new value to the sum of all previous values. For example, day 1 sales plus day 2 sales, then add day 3 sales, and so on.

Result

You get a running total that grows as you move through the sequence.

This step-by-step addition helps track progress over time, which is more informative than isolated daily numbers.

3

Intermediate

Creating running totals in Tableau

🤔Before reading on: do you think running totals in Tableau require writing complex formulas or just using built-in features? Commit to your answer.

Concept: Tableau provides built-in table calculations to create running totals easily without complex formulas.

In Tableau, you can create a running total by right-clicking a measure, selecting 'Quick Table Calculation', then choosing 'Running Total'. Tableau automatically sums values cumulatively along the chosen dimension, like dates.

Result

Your chart updates to show cumulative sums, making trends over time clear.

Knowing Tableau’s built-in tools saves time and reduces errors compared to manual calculations.

4

Intermediate

Customizing running total direction

🤔Before reading on: do you think running totals always add values from oldest to newest, or can the direction be changed? Commit to your answer.

Concept: You can control the direction and partitioning of running totals in Tableau to fit your data layout.

Tableau lets you set the direction of running totals (e.g., left to right, top to bottom) and how data is grouped (partitioned). This is done in the 'Edit Table Calculation' dialog, where you specify addressing and partitioning fields.

Result

Running totals reflect the correct sequence and grouping, matching your analysis needs.

Controlling direction and partitioning ensures running totals are accurate and meaningful for your specific data structure.

5

Intermediate

Handling running totals with filters

🤔Before reading on: do you think filters always apply before or after running totals? Commit to your answer.

Concept: Filters can affect running totals differently depending on when they apply in Tableau’s calculation order.

Filters that remove data before running totals are calculated change the cumulative sums. Tableau processes filters in a specific order: dimension filters apply before table calculations, but measure filters apply after. Using context filters can control this behavior.

Result

You get running totals that reflect the filtered data correctly.

Understanding filter order prevents mistakes where running totals show unexpected results due to hidden data.

6

Advanced

Combining running totals with other calculations

🤔Before reading on: do you think running totals can be combined with averages or percentages easily in Tableau? Commit to your answer.

Concept: Running totals can be combined with other table calculations like moving averages or percent of total for deeper insights.

You can layer multiple table calculations by creating calculated fields or using nested quick table calculations. For example, calculate running total first, then compute percent of total on that running sum.

Result

Your dashboard shows complex metrics like cumulative growth rates or share of total over time.

Mastering combined calculations unlocks powerful analysis beyond simple sums.

7

Expert

Performance and pitfalls of running totals

🤔Before reading on: do you think running totals always perform well on large datasets, or can they slow down dashboards? Commit to your answer.

Concept: Running totals can impact performance and have subtle issues with data granularity and sorting in Tableau.

Running totals require Tableau to process data in order, which can slow dashboards with millions of rows. Also, incorrect sorting or missing data points can cause wrong cumulative sums. Using data extracts, proper indexing, and careful sorting helps. Understanding Tableau’s calculation order and partitioning is critical to avoid errors.

Result

Efficient, accurate running totals that scale and reflect true cumulative values.

Knowing performance and data issues prevents slow or misleading dashboards in real projects.

Under the Hood

Tableau calculates running totals using table calculations that process data in a specified order and direction. It sums each value with all previous values in the partition, respecting sorting and partitioning fields. Internally, Tableau builds a temporary data structure to accumulate sums as it renders the view.

Why designed this way?

Tableau uses table calculations for running totals to keep data flexible and interactive without changing the underlying data source. This design allows fast recalculations when filters or dimensions change, supporting dynamic dashboards.

┌───────────────┐
│ Raw Data Rows │
└──────┬────────┘
       │
       ▼
┌─────────────────────────────┐
│ Tableau Table Calculation    │
│ - Partition by dimension     │
│ - Sort in sequence           │
│ - Sum cumulatively           │
└──────┬──────────────────────┘
       │
       ▼
┌─────────────────────────────┐
│ Running Total Output         │
│ - Cumulative sums per row    │
└─────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do running totals always start from zero at the beginning of the data? Commit to yes or no.

Common Belief: Running totals always start from zero and add up all values from the very first data point.

Reality: Running totals start over for each partition or group defined in Tableau, not necessarily from the very first data point in the entire dataset.

Why it matters: Ignoring partitions can cause wrong totals that mix unrelated groups, leading to incorrect business conclusions.

Quick: Do filters always apply before running totals? Commit to yes or no.

Common Belief: Filters always remove data before running totals are calculated, so totals reflect filtered data.

Reality: Some filters apply after running totals (like measure filters), so running totals may include filtered-out data unless context filters are used.

Why it matters: Misunderstanding filter order causes running totals to show unexpected or misleading values.

Quick: Can running totals be calculated without sorting data? Commit to yes or no.

Common Belief: Running totals work correctly regardless of data sorting.

Reality: Running totals depend on the order of data; incorrect sorting leads to wrong cumulative sums.

Why it matters: Wrong sorting can make running totals jump or decrease unexpectedly, confusing users.

Quick: Are running totals always fast on large datasets? Commit to yes or no.

Common Belief: Running totals are simple sums and always perform well, even on big data.

Reality: Running totals can slow down dashboards on large datasets because Tableau must process data row by row in order.

Why it matters: Ignoring performance can cause slow, unresponsive dashboards frustrating users and delaying decisions.

Expert Zone

1

Running totals reset automatically when partitioning fields change, which can be used creatively to compare groups side-by-side.

2

Tableau’s calculation order means that running totals can be affected by other calculations or filters in subtle ways, requiring careful design.

3

Performance tuning for running totals often involves balancing data extracts, aggregation levels, and calculation complexity.

When NOT to use

Avoid running totals when data is unordered or when you need non-cumulative metrics like daily averages. Instead, use simple aggregations or moving averages. Also, for very large datasets, consider pre-aggregating data in the source or using incremental extracts.

Production Patterns

Professionals use running totals in sales dashboards to show cumulative revenue, in finance to track cumulative expenses, and in operations to monitor resource usage over time. They combine running totals with filters and parameters to create interactive, dynamic reports.

Connections

Moving average

Builds-on

Understanding running totals helps grasp moving averages, which also use sequences but smooth data instead of accumulating it.

Incremental backups (IT)

Same pattern

Running totals and incremental backups both accumulate changes step-by-step, showing how cumulative processes appear across fields.

Financial compound interest

Similar cumulative growth

Running totals resemble how compound interest adds accumulated value over time, linking BI concepts to finance.

Common Pitfalls

#1 Running total ignores sorting, causing wrong cumulative sums.

Wrong approach:

Using running total without setting sort order in 'Edit Table Calculation', leading to sums jumping around.

Correct approach:

Set correct sort order (e.g., by date ascending) in 'Edit Table Calculation' to ensure proper cumulative addition.

Root cause:

Misunderstanding that running totals depend on data order, not just values.

#2 Applying filters that remove data after running total calculation, causing totals to include filtered-out values.

Wrong approach:

Using measure filters without context filters, expecting running totals to exclude filtered data.

Correct approach:

Use context filters to filter data before running totals calculate, ensuring accurate cumulative sums.

Root cause:

Not knowing Tableau’s filter and calculation order.

#3 Trying to calculate running total on unsorted or non-sequential data.

Wrong approach:

Applying running total on data sorted randomly or by irrelevant fields.

Correct approach:

Sort data by the sequence dimension (e.g., date) before applying running total.

Root cause:

Ignoring the importance of sequence in cumulative calculations.

Key Takeaways

Running totals show cumulative sums that help track progress over sequences like time.

Tableau’s built-in running total feature simplifies cumulative calculations without complex formulas.

Correct sorting and partitioning are essential for accurate running totals.

Filters affect running totals differently depending on when they apply; context filters help control this.

Running totals can impact performance on large datasets and require careful design to avoid errors.