Running total in Tableau - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to see how sales have accumulated month by month over the year to understand growth trends.

📊 Data: You have monthly sales data for the year 2023, including the month and total sales amount.

🎯 Deliverable: Create a line chart showing the running total of sales by month for 2023.

Sample Data

Month	Sales
January	1000
February	1500
March	1200
April	1700
May	1600
June	1800
July	2000
August	2100
September	1900
October	2200
November	2300
December	2500

1

Step 1: Connect your data source with the monthly sales data into Tableau.

Load the data table with columns 'Month' and 'Sales'.

Expected Result

Data is available in Tableau with 12 rows for each month.

2

Step 2: Create a calculated field for the running total of sales.

Name: Running Total Sales Formula: RUNNING_SUM(SUM([Sales]))

Expected Result

A new field 'Running Total Sales' that sums sales cumulatively by month.

3

Step 3: Build a line chart with Month on Columns and Running Total Sales on Rows.

Drag 'Month' to Columns shelf. Drag 'Running Total Sales' to Rows shelf. Ensure 'Month' is sorted chronologically.

Expected Result

Line chart shows sales accumulating from January to December.

4

Step 4: Format the chart for clarity and add axis titles.

Set X-axis title to 'Month'. Set Y-axis title to 'Cumulative Sales'. Add chart title: 'Running Total of Sales by Month - 2023'.

Expected Result

Clear, labeled line chart ready for presentation.

Final Result

Month:    Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sep  Oct  Nov  Dec
Sales:    1000 1500 1200 1700 1600 1800 2000 2100 1900 2200 2300 2500
Running:  1000 2500 3700 5400 7000 8800 10800 12900 14800 17000 19300 21800

✓Sales steadily increased over the year with some months showing larger jumps.

✓The running total line chart clearly shows cumulative growth month by month.

✓December had the highest cumulative sales reaching 21,800.

Bonus Challenge

Create a running total of sales but reset the total at the start of each quarter.

Show Hint

Use the RUNNING_SUM function with a partition by quarter to restart the sum every 3 months.

Practice

1. What does the RUNNING_SUM() function do in Tableau?

easy

A. Calculates a cumulative total by adding values step-by-step

B. Finds the average of all values in a column

C. Counts the number of rows in a dataset

D. Filters data based on a condition

Solution

1. Step 1: Understand the purpose of RUNNING_SUM()

   RUNNING_SUM() adds values cumulatively across a sorted dimension, like dates.

2. Step 2: Compare with other functions

   Unlike average or count, RUNNING_SUM() accumulates values step-by-step.

3. Final Answer:

   Calculates a cumulative total by adding values step-by-step -> Option A

4. Quick Check:

   RUNNING_SUM() = cumulative total [OK]

Hint: Think 'running total' means adding values one after another [OK]

Common Mistakes:

• Confusing RUNNING_SUM() with average or count functions
• Thinking it filters data instead of summing cumulatively
• Assuming it resets for each row instead of accumulating

2. Which of the following is the correct syntax to create a running total of Sales in Tableau?

easy

A. RUNNING_SUM(SUM([Sales]))

B. SUM(RUNNING_SUM([Sales]))

C. RUNNING_TOTAL(SUM([Sales]))

D. TOTAL_SUM([Sales])

Solution

1. Step 1: Identify correct function usage

   RUNNING_SUM() wraps an aggregation like SUM() to calculate cumulative totals.

2. Step 2: Check syntax correctness

   RUNNING_SUM(SUM([Sales])) uses RUNNING_SUM(SUM([Sales])) which is valid; others use incorrect or non-existent functions.

3. Final Answer:

   RUNNING_SUM(SUM([Sales])) -> Option A

4. Quick Check:

   RUNNING_SUM(SUM()) is correct syntax [OK]

Hint: RUNNING_SUM wraps an aggregation like SUM inside [OK]

Common Mistakes:

• Using RUNNING_TOTAL instead of RUNNING_SUM
• Placing SUM outside RUNNING_SUM incorrectly
• Using non-existent functions like TOTAL_SUM

3. Given the following daily sales data:

Date | Sales
Jan 1 | 100
Jan 2 | 150
Jan 3 | 200

What is the running total on Jan 3 using RUNNING_SUM(SUM([Sales]))?

medium

A. 200

B. 150

C. 450

D. 100

Solution

1. Step 1: Calculate daily sums

   Sales are 100 on Jan 1, 150 on Jan 2, and 200 on Jan 3.

2. Step 2: Compute running total up to Jan 3

   Running total = 100 + 150 + 200 = 450.

3. Final Answer:

   450 -> Option C

4. Quick Check:

   100+150+200 = 450 [OK]

Hint: Add all previous sales including current date [OK]

Common Mistakes:

• Taking only current day sales instead of cumulative
• Adding only two days instead of three
• Confusing running total with daily sales

4. You created a running total using RUNNING_SUM(SUM([Sales])) but the values reset unexpectedly for each category. What is the likely cause?

medium

A. SUM([Sales]) is incorrect syntax

B. The table calculation is partitioned by category, causing reset

C. RUNNING_SUM() cannot be used with categories

D. Data source has missing sales values

Solution

1. Step 1: Understand table calculation partitioning

   Table calculations like RUNNING_SUM reset when partitioned by a dimension, here category.

2. Step 2: Identify cause of reset

   If partitioning is by category, running total restarts for each category separately.

3. Final Answer:

   The table calculation is partitioned by category, causing reset -> Option B

4. Quick Check:

   Partitioning causes running total reset [OK]

Hint: Check partitioning settings if running total resets [OK]

Common Mistakes:

• Assuming syntax error causes reset
• Believing RUNNING_SUM can't work with categories
• Ignoring partitioning in table calculations

5. You want to show a running total of monthly sales but only for the current year. Which approach correctly applies this filter without breaking the running total calculation?

hard

A. Use RUNNING_SUM(SUM([Sales])) without any filters

B. Apply a regular filter for current year after creating the running total

C. Create a calculated field that sums sales only if year equals current year, then apply RUNNING_SUM

D. Use a context filter for current year before applying RUNNING_SUM(SUM([Sales]))

Solution

1. Step 1: Understand filter order impact

   Regular filters applied after table calculations can break running totals by removing data points.

2. Step 2: Use context filter to limit data first

   Context filters limit data before calculations, preserving running total logic for current year only.

3. Final Answer:

   Use a context filter for current year before applying RUNNING_SUM(SUM([Sales])) -> Option D

4. Quick Check:

   Context filter preserves running total correctness [OK]

Hint: Filter data first with context filter, then run running total [OK]

Common Mistakes:

• Applying regular filter after running total breaks calculation
• Not filtering data, showing all years
• Using calculated field without proper filtering