Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 35 ยท Calculations

Tableau Table Calculations: Running Total, Rank, Percent of Total

Learn Tableau table calculations: quick table calcs, Compute Using (partitioning and addressing), LOOKUP, RUNNING_SUM, WINDOW functions, RANK and percent of total, with pandas stand-ins.

A table calculation works on the numbers already in the view. Instead of asking the database for a new value, Tableau takes the aggregated results (the "table" of marks) and computes something across them: a running total, the change from the previous month, a rank, a moving average, a share of the total.

This is the feature that confuses most beginners, because the answer depends on which direction the calculation runs. Once you understand that one idea, partitioning and addressing, the rest is a menu.

Tableau is not available here, so you will see the calculations as read-only code with pandas stand-ins that give the same numbers.

Quick table calculations

The fastest way to create one: right-click a measure pill on the view > Quick Table Calculation and choose:

Quick calc What it does
Running Total Cumulative sum along the direction
Difference Value minus the previous value
Percent Difference Change relative to the previous value
Percent of Total Value divided by the total of its partition
Rank Position by value (1 = highest by default)
Percentile Percentile rank of each value
Moving Average Average of the current and the previous N values
YTD Total Running total that restarts each year
Compound Growth Rate Growth per period from the first value
Year Over Year Growth Change against the same period last year

A triangle (ฮ”) appears on the pill: that marks it as a table calculation. Choose Edit Table Calculation... to change the settings.

Compute Using: partitioning and addressing

Every table calculation has two groups of dimensions:

  • Addressing: the dimensions the calculation moves along. A running total along Month adds month after month.
  • Partitioning: the dimensions that split the data into separate calculations. If Region partitions, each region has its own running total that restarts.

In the dialog Compute Using picks the addressing:

  • Table (Across) / Table (Down): along the whole table, restarting for each row / column.
  • Pane (Across) / Pane (Down): along each pane only.
  • Cell: only within each cell (usually useless).
  • Specific Dimensions: tick the dimensions that you want in the addressing; all others become the partitioning. This is the most explicit choice, and the one to use when the layout is not obvious.
flowchart LR
    V["Marks in the view<br/>Region x Month"] --> P["Partition by Region<br/>(separate calculations)"]
    P --> A["Address along Month<br/>(the order of the running total)"]
    A --> R["Result for each mark"]

Partitioning decides which marks calculate together; addressing decides the direction inside each partition.

When a table calculation gives strange numbers, the cause is nearly always the wrong Compute Using. Switch to Specific Dimensions and tick the field that should run along the axis.

Try it: running total, difference and percent difference

The pandas stand-in groups by region (partition) and works along month (addressing).

Python Stand-in: partition by region, address along month

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The first month of each region shows NaN (Null in Tableau) for the difference because there is no previous value. If you changed the addressing to region, the running total would run across regions instead, which is rarely meaningful.

Table calculation functions

You can write them yourself in a calculated field, for example to build a custom ratio. The most common:

TEXT
RUNNING_SUM(SUM([Sales]))                       // cumulative sum
RUNNING_AVG(SUM([Sales]))
WINDOW_SUM(SUM([Sales]), -2, 0)                 // sum of the 3 latest values
WINDOW_AVG(SUM([Sales]), -2, 0)                 // 3-point moving average
LOOKUP(SUM([Sales]), -1)                        // previous value (+1 next)
LOOKUP(SUM([Sales]), FIRST())                   // first value of the partition
INDEX()  SIZE()  FIRST()  LAST()                // position helpers
RANK(SUM([Sales]))   RANK_DENSE(...)   RANK_UNIQUE(...)   RANK_PERCENTILE(...)
TOTAL(SUM([Sales]))                             // the total of the partition

FIRST() returns the offset from the current row to the first row, so LOOKUP(x, FIRST()) means "the value of the first row". WINDOW_SUM(x, -2, 0) sums from two rows back to the current row.

Percent of total written as a calculation:

TEXT
SUM([Sales]) / TOTAL(SUM([Sales]))

Set Compute Using to the dimension whose members add up to 100 percent.

Try it: moving average, rank and a custom percent of total

Python Stand-in: WINDOW_AVG, RANK and TOTAL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Rank defaults to competition ranking (ties share a rank, the next rank is skipped); RANK_DENSE does not skip numbers and RANK_UNIQUE gives each mark a different number.

Table calculations in the order of operations

A table calculation is computed last, after the data has been fetched, filtered and aggregated. Three consequences follow:

  1. Dimension filters change the input. If you filter out March, the running total simply skips it. The calculation only sees what is left.
  2. To keep earlier rows for the calculation but hide them, filter with a table calculation filter such as LOOKUP(MIN([Order Date]), 0) or use FIRST() and LAST() in a filter so rows are hidden after computing.
  3. Totals and reference lines are computed afterwards too. See Filters and the order of operations.

Nested and advanced calculations

You can build a table calculation on top of another: right-click the pill > Add Table Calculation... (secondary calculation). Example: first compute Percent Difference along Month, then rank those growth rates along Region.

Table calculations can also pass through a data blend or reference parameters, but they cannot be used as dimensions in a LOD expression because they do not exist in the data source. They are also limited when the view is not aggregated.

Common mistakes

  • Wrong Compute Using, which gives a running total across regions.
  • Filtering the first rows away and getting a Percent Difference of Null for the new first value.
  • Using a table calculation when a LOD would be correct, for example a customer-level number that must ignore the view's other dimensions (LOD expressions).
  • Moving averages with missing periods, because Tableau averages the marks that exist (use Show Missing Values on the date).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is a table calculation?

    Tableau computes table calculations after the data is fetched and aggregated, on the marks of the view.
  2. 2. In a running total by Region and Month with Compute Using = Month, what is the partitioning?

    The address is Month (the direction), so Region is left over as the partition; each region gets its own running total.
  3. 3. Which expression returns the previous mark's value?

    LOOKUP with offset -1 reads the value one mark earlier along the addressing direction.
  4. 4. A percent-of-total table calculation gives numbers that don't add up to 100 within each region. What is the likely fix?

    The percentages add up along the addressing dimensions; check Compute Using.

Summary

  • Table calculations compute on the marks in the view, after aggregation and most filters.
  • Partitioning splits the calculations; addressing sets the direction. Use Compute Using > Specific Dimensions to be explicit.
  • Quick calcs (running total, difference, percent of total, rank, moving average, YTD, YoY) cover most needs; LOOKUP, WINDOW_, RUNNING_, INDEX and TOTAL give you custom control.
  • Wrong numbers almost always mean wrong Compute Using.

Next: LOD expressions