This lesson on Table Calculations is hands-on and example-driven. You will apply Quick Table Calculations—including Running Total, Difference, and Moving Average—to analyze cumulative growth, period-over-period variance, and trend direction. You will also manipulate multiple measure pills on shelves to manage independent Marks cards for clear before-and-after visual comparisons.
What You'll Be Able To Do
- Apply a Running Total quick table calculation to compute cumulative measure values over a continuous timeline.
- Calculate period-over-period variance using the Difference quick table calculation.
- Smooth noisy daily time-series data using an adjustable Moving Average window.
- Configure multiple measure pills on shelves to manipulate independent Marks cards, mark types, and labels.
- Adjust lookback window parameters by opening the Edit Table Calculation dialog.
Detailed Concept Walkthrough
1. Running Total Calculations
A Running Total computes an ongoing cumulative aggregation across an ordered dimension such as time. It transforms discrete point-in-time metrics into total accumulated value up to each point.
- Mechanism: Tableau evaluates each row or mark sequentially along the partition, adding the current mark's aggregated value to the sum of all preceding marks.
- Under the Hood: Table calculations execute locally in Tableau on the aggregated cache returned by the data source, rather than generating an expensive multi-pass SQL query.
- Best Practice: Use continuous date fields on columns so the cumulative line displays as an unbroken, chronological progression.
// Equivalent calculated field for Quick Table Calculation: Running Total
// Computes cumulative sum of Sales across the visual timeline
RUNNING_SUM(SUM([Sales]))
Key Takeaway: Running totals highlight cumulative milestones that individual periodic spikes obscure.
2. Difference Calculations
The Difference calculation measures absolute period-over-period change between adjacent data points. It quantifies velocity and direction of change over time.
- Mechanism: Tableau subtracts the prior mark's aggregated value from the current mark's value (Value_t minus Value_t-1).
- Under the Hood: The first mark in the partition evaluates to null because no preceding offset exists to subtract from.
- Best Practice: Switch mark types from line to bar charts when showing differences so positive gains and negative drops visually baseline at zero.
// Equivalent calculated field for Quick Table Calculation: Difference
// ZN handles nulls; LOOKUP retrieves the previous offset value (-1)
ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)
Key Takeaway: Difference calculations isolate periodic volatility and growth trajectories from absolute scale.
3. Moving Average and Noise Smoothing
A Moving Average smooths short-term fluctuations and seasonality by averaging values within a sliding lookback window. It reveals underlying macroeconomic or operational trends.
- Mechanism: For each mark, Tableau calculates the mean across a specified number of previous periods plus the current period.
- Under the Hood: Tableau updates the window boundary dynamically for every point along the calculation direction.
- Best Practice: Right-click the table calculation pill and select Edit Table Calculation to fine-tune the previous periods lookback parameter based on granularity.
// Equivalent calculated field for Quick Table Calculation: Moving Average
// Averages current point and previous 13 periods (e.g., 14-day / 2-week window)
WINDOW_AVG(SUM([Sales]), -13, 0)
Key Takeaway: Expanding the lookback window increases curve smoothness but increases lag relative to sudden shifts.
4. Multiple Measures and Marks Shelf Management
Placing duplicate measures on the Rows shelf creates distinct visualization panels that can be styled and calculated independently.
- Mechanism: Adding multiple measure pills generates an 'All' marks card alongside dedicated mark cards for each individual pill instance.
- Under the Hood: Property adjustments on the 'All' card apply globally, whereas edits on an individual pill card affect only that visual layer.
- Best Practice: Hold Ctrl while dragging an active pill to duplicate it instantly to another shelf or to the Label shelf.
// Workflow: Duplicate measure for before/after comparison
// 1. Drag [Sales] to Rows shelf twice -> SUM([Sales]), SUM([Sales])
// 2. Right-click second pill -> Quick Table Calculation -> [Running Total / Moving Avg]
// 3. Ctrl + Drag second pill to its Marks Card -> Label
Key Takeaway: Isolating marks cards allows side-by-side visual validation of raw aggregations against transformed table calculations.
Topics Covered in Table Calculations
- Continuous Dates and Duplicate Measures (0:00 - 0:45) — The instructor sets Order Date to continuous month and duplicates Sales onto Rows to prepare a comparison layout.
- Applying Running Total (0:45 - 1:50) — A Running Total quick table calculation is applied to the bottom Sales pill to display accumulated sales over time.
- Managing Independent Marks Cards (1:50 - 2:35) — The instructor uses Ctrl-drag to duplicate pills onto Label shelves and formats individual Marks cards independently.
- Calculating Differences Over Time (2:35 - 3:35) — The measure is switched to a bar chart and converted to a Difference quick table calculation to show period-to-period deltas.
- Smoothing Noise with Moving Average (3:35 - 4:45) — A granular daily Sales line is transformed with a Moving Average quick table calculation to eliminate noise.
- Editing Calculation Parameters (4:45 - 5:30) — The lookback window is customized via Edit Table Calculation, followed by a preview of dual-axis overlays.
Reference Cheat Sheet
-
RUNNING_SUM(SUM([Sales]))— Calculates cumulative total across marksRUNNING_SUM(SUM([Sales])) -
ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)— Calculates difference from previous markZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1) -
WINDOW_AVG(SUM([Sales]), -k, 0)— Calculates sliding window average across k periodsWINDOW_AVG(SUM([Sales]), -13, 0) -
Ctrl + Drag (Pill)— Duplicates pill onto another shelf// Ctrl + Drag [Sales] to Label card -
Right-Click Pill -> Quick Table Calculation— Applies one-click secondary calculation// Select: Running Total | Difference | Moving Average -
Right-Click Pill -> Edit Table Calculation— Configures calculation window and offsets// Adjust: Previous Values = 13, Current Value = Included
Comparison Table
| Calculation | Analytical Purpose | Data Transformation |
|---|---|---|
| Running Total | Track cumulative growth | Accumulates all prior marks |
| Difference | Measure periodic change | Subtracts previous mark value |
| Moving Average | Filter out noise | Averages sliding historical window |
Common Pitfalls
- Mistake: Modifying mark properties on the All marks card instead of a specific measure card. Avoid: Select the target measure marks card before changing mark types, colors, or labels.
- Mistake: Setting moving average lookback window too wide, completely erasing meaningful demand spikes. Avoid: Test progressive window sizes in Edit Table Calculation to preserve genuine inflection points.
- Mistake: Expecting table calculations to alter underlying data source queries. Avoid: Remember table calculations compute strictly on local aggregated cache returned into the view.
FAQs
- Why is the first mark blank when using a Difference table calculation? Difference requires a preceding mark to compute delta; because no prior mark exists for the first data point, it evaluates to null.
- How do I change how many previous periods are included in a Moving Average? Right-click the measure pill with the table calculation delta icon, choose Edit Table Calculation, and modify the Previous Values lookback count.
- How can I display raw values and calculated values on the same sheet simultaneously? Drag the measure to the Rows shelf twice, apply the table calculation only to the second pill, and format the marks cards independently.