Module 6.2

Calculations & Parameters

This hands-on lesson guides beginners through creating and testing calculated fields, applying table calculations (running totals, moving averages, percent-of-total), and using LOD expressions (FIXED, INCLUDE, EXCLUDE) to control aggregation. You will also learn how to add parameters to drive interactivity—Top N filters, measure switching, and threshold-based highlights—using clear, step-by-step examples and practice tasks, plus an interactive demo to try what you build.

40 min
Intermediate
Hands-on
What You'll Learn
  • Write basic and advanced calculated fields
  • Use table calculations for running totals and moving averages
  • Understand FIXED/INCLUDE/EXCLUDE LOD expressions
  • Create parameters to control calculations and filters
  • Build an interactive parameter-driven demo
Contents
01

Calculated fields

Calculated fields let you create custom metrics and business logic in Tableau by using expressions that run at the chosen aggregation level. They can operate at the row level, at an aggregated level, or on dates and strings. Use calculated fields to standardize metrics, handle missing values, and drive conditional formatting or flags in your views. Start with simple expressions and validate results on sample data before applying to full dashboards.

What is a calculated field?

A calculated field is a custom field defined by an expression. It can reference other fields, constants, parameters, and functions to compute new values used in views and downstream calculations.

Common patterns

Ratio / rate
[Profit Ratio] = SUM([Profit]) / SUM([Sales])

Format as percent and guard against zero denominators with NULLIF when needed.

Row-level logic
[Is High Margin] = IF [Profit] / [Sales] > 0.2 THEN 'High' ELSE 'Normal' END

Use row-level flags for conditional formatting and filtering within the view.

Date extraction
[Order Year] = YEAR([Order Date])

Extract date parts to group or filter time-based analyses.

Null safety
[Profit Margin] = IF SUM([Sales]) = 0 THEN 0 ELSE SUM([Profit]) / SUM([Sales]) END

Always handle zero or missing values to avoid errors and misleading metrics.

Examples

Profit ratio (aggregate)
SUM([Profit]) / NULLIF(SUM([Sales]), 0)

Use NULLIF to avoid divide-by-zero and format as percent when presenting ratios.

Row-level flag
IF [Profit] / [Sales] > 0.2 THEN 'High' ELSE 'Normal' END

Compute flags at row level to segment and color marks in the view.

Table calculations run after Tableau aggregates data for the view and operate on the result set. They are ideal for computations like running totals, moving averages, percent of total, and differences from previous periods. Correct partitioning and addressing determine where the calculation is applied and in which direction it moves. When used carefully, table calculations enable advanced time series and cohort analyses without changing the underlying data source.

What is a table calculation?

A table calculation is a transformation that operates on the aggregated results produced by the view. It depends on partitioning and addressing settings to define the computation window.

Examples

Running total
RUNNING_SUM(SUM([Sales]))

Accumulate values across the partition direction.

Percent of total
SUM([Sales]) / WINDOW_SUM(SUM([Sales]))

Divide by WINDOW_SUM for percent contribution within the partition.

Difference from previous
SUM([Sales]) - LOOKUP(SUM([Sales]), -1)

Compute change compared to the previous row in the partition.

Moving average (3)
WINDOW_AVG(SUM([Sales]), -2, 0)

Smooth short-term fluctuations with a 3-period window.

Row index
INDEX()

Return position within the partition for ranking or offset calculations.

Note: After adding a table calculation, right-click the pill and use Compute Using to set partitioning and addressing. Visualize partitions by temporarily adding partition fields to the view.

Practice: Table calculations

Task: Build a monthly sales line chart using ../../assets/data/sample_sales.csv.

Show Solution

Ensure dates are parsed and sorted, and use MONTH(Order Date) or a date truncation to group by month.

Task: Add RUNNING_SUM(SUM([Sales])) and set Compute Using to Table Across to create a cumulative series.

Show Solution

Use Table Across when months are in columns; otherwise use Specific Dimensions selecting the month field.

Task: Create SUM([Sales]) / WINDOW_SUM(SUM([Sales])) and format as percent.

Show Solution

Use WINDOW_SUM for the denominator and verify partitioning matches desired scope.

03

LOD expressions (FIXED, INCLUDE, EXCLUDE)

Level of detail (LOD) expressions let you compute aggregations at a specific granularity independent of the view. Use FIXED to anchor an expression to particular dimensions, INCLUDE to add dimensions for the calculation, and EXCLUDE to remove dimensions from the aggregation. LODs are powerful for metrics like per-customer totals, segment-level averages, and percent contributions. Keep in mind their interaction with filters and table calculations to avoid surprises.

What is an LOD expression?

Level of detail (LOD) expressions let you compute a value at a specific set of dimensions, independent of how the current view is grouped. For example, you can calculate total sales per customer even when your worksheet shows data by month or product. LODs are evaluated before table calculations and are not affected by normal filters unless those filters are placed in Context.

Analogy: Think of an LOD as taking a snapshot aggregation grouped by chosen fields, then using that snapshot inside your main view.

Quick example: { FIXED [Customer ID] : SUM([Sales]) } computes total sales per customer regardless of the view.

Types & examples

FIXED

Anchor an aggregation to specific dimensions regardless of the view.

{ FIXED [Customer ID] : SUM([Sales]) }
INCLUDE

Add dimensions to the calculation to compute at a finer granularity.

{ INCLUDE [Product] : SUM([Sales]) }
EXCLUDE

Remove dimensions from the aggregation to compute at a coarser level.

{ EXCLUDE [Category] : SUM([Sales]) }
# Average order value per customer
{ FIXED [Customer ID] : SUM([Sales]) }  # sample output: 523.45

# Percent of category by product
SUM([Sales]) / { FIXED [Category] : SUM([Sales]) }  # sample: 0.23 (23%)
Gotcha: FIXED LODs are computed before most filters. To make a filter affect an LOD, convert the filter to a context filter.

Practice: LOD expressions

Task: Create a view by Category and Product with SUM(Sales).

Show Solution

Use Category and Product on rows with SUM(Sales) on columns or text to verify aggregation.

Task: Create Category Sales: { FIXED [Category] : SUM([Sales]) } and add it to the view.

Show Solution

Use the FIXED expression and verify it returns the same value per Category regardless of the view's detail.

Task: Create % of Category: SUM([Sales]) / [Category Sales] and format as percent.

Show Solution

Verify the denominator uses the FIXED LOD and watch for context filter effects.

04

Parameters for interactivity

Parameters are user-controlled inputs that can feed values into calculated fields, filters, bins, and reference lines to make dashboards interactive. Parameters allow users to drive Top N filters, switch measures, and explore 'what-if' scenarios without modifying the data source.

Common uses

Top N filter

Integer parameter [Top N] with RANK_UNIQUE to show the top N items.

Switch measure

String parameter [Measure] + CASE expression to swap measures (Sales / Profit).

Dynamic threshold

Numeric parameter [Threshold] used in calculated fields to highlight or filter records above a value.

Examples

Top N filter
RANK_UNIQUE(SUM([Sales])) <= [Top N]

Combine integer parameter with rank functions to show top N items.

Switch measure
CASE [Measure]
 WHEN 'Sales' THEN SUM([Sales])
 WHEN 'Profit' THEN SUM([Profit])
END

Use a string parameter and a CASE statement to let users select different metrics.

Threshold highlight
IF SUM([Profit]) / SUM([Sales]) > [Threshold] THEN 'Yes' ELSE 'No' END

Drive highlights and conditional formatting using numeric parameters.

Practice Questions: Parameters

Task: Create a Top N parameter (1-20) and implement a parameter-driven filter to show top N products.

Show Solution

Create an integer parameter and filter using RANK or RANK_UNIQUE on the chosen metric compared to the parameter value.

Task: Create a Measure parameter and use it to swap measures on a single chart using a CASE statement.

Show Solution

Use a string parameter and CASE in a calculated field to return different aggregated expressions based on the parameter value.

Task: Create a numeric Threshold parameter and use it to highlight records above the threshold with a boolean calculated field.

Show Solution

Use the parameter inside an IF statement to return a boolean and apply color or a filter based on the result.

Interactive demo

Choose a demo type and interact with the controls to see the parameter-driven calculation update in real time.

Demo code / Output
// demo code updates here

Key Takeaways

Calculated fields

Create fields to compute dynamic metrics

Table calculations

Apply computations across table directions

LOD expressions

Control aggregation at specific granularities

Parameters

Make dashboards interactive and flexible

Best practices

Use clear naming and comments in calculations

Testing

Validate logic with small datasets before production

Knowledge Check

Test your understanding of Tableau calculations and parameters.

Question 1 of 6

Which expression computes a value fixed at the Customer level regardless of the view?

Question 2 of 6

To compute a running total across months, which Compute Using option is appropriate?

Question 3 of 6

What does WINDOW_SUM(SUM([Sales])) compute?

Question 4 of 6

Which is a typical use of a parameter in Tableau?

Question 5 of 6

To make a FIXED LOD respect a filter, what should you do?

Question 6 of 6

Which function returns a value from a previous row in a partition?

Answer all questions to check your score