6.2 Essential Table Calculations: Running, Window & Percent of Total

6.2 Essential Table Calculations: Running, Window & Percent of Total

Table calculations operate on the aggregated marks returned for a view. They do not add detail rows to the source query. Their meaning therefore depends on the view's dimensions, sort order, addressing direction, and partition boundaries. Before selecting a function, state which marks the calculation should traverse and where it should restart.

Running calculations

A running total accumulates from the first addressed mark through the current mark. The explicit form is RUNNING_SUM(SUM([Sales])). If Month is addressed left to right and Region partitions the view, each region receives its own cumulative monthly series. RUNNING_AVG, RUNNING_MIN, RUNNING_MAX, and RUNNING_COUNT use the same addressing concept.

Running totals require a meaningful order. A date axis sorted chronologically is appropriate; an arbitrary alphabetical product order usually is not. Missing periods also matter. If February has no mark, Tableau does not invent a February value unless the date domain is completed or densified by the view.

Window calculations

WINDOW_SUM, WINDOW_AVG, WINDOW_MIN, WINDOW_MAX, WINDOW_MEDIAN, and related functions evaluate a frame within the partition. The optional start and end offsets are relative to the current mark, which is zero. Negative offsets look backward and positive offsets look forward.

For a three-month trailing average including the current month, use:

WINDOW_AVG(SUM([Sales]), -2, 0)

The frame contains offsets -2, -1, and 0. At the beginning of the partition, fewer than three marks may be available, so decide whether a partial window is acceptable. WINDOW_SUM(SUM([Sales]), FIRST(), LAST()) spans the partition; a fixed frame can be narrower.

Difference and percent difference

Difference From compares the current aggregate with another mark, commonly the previous one. An explicit difference is SUM([Sales]) - LOOKUP(SUM([Sales]), -1). Percent Difference From divides that difference by an appropriate prior value. Protect the denominator against zero and decide whether ABS is consistent with the business definition.

LOOKUP returns null when its offset falls outside the partition. The first month of a previous-month comparison therefore has no prior result. That is expected, not a calculation failure. If a normal dimension filter removes the prior month before table calculations run, LOOKUP also returns null; a late table-calculation filter can hide marks without removing the history needed for the comparison.

Percent of total and TOTAL

A common percent-of-total formula is:

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

TOTAL returns the total of the supplied expression across its configured table-calculation partition. With Category addressed within Region, each category can be divided by its regional partition total. If Compute Using instead spans the full table, the denominator changes. Confirm that the expected marks sum to 100%.

TOTAL and WINDOW_SUM may produce the same result for additive SUM values over the same full partition. Neither should be assumed to repair an invalid aggregation merely because its name contains Total. Non-additive measures such as COUNTD, MEDIAN, and ratios require a calculation designed at the correct data grain. A displayed grand total can also use Tableau's total aggregation behavior and should be tested separately.

Rank, percentile, and index

RANK, RANK_DENSE, RANK_MODIFIED, RANK_PERCENTILE, and RANK_UNIQUE handle ties differently. RANK_DENSE produces 1, 2, 2, 3; ordinary RANK produces 1, 2, 2, 4. Set ascending or descending order explicitly and compute along the entity being ranked within the intended partition.

INDEX returns the position of the current mark in the partition, starting at one. It is useful for display logic and some ranking layouts but changes when sorting or partitioning changes. SIZE returns the number of marks in the partition. FIRST and LAST return offsets from the current mark to the first and last marks.

Percentile can mean a rank percentile table calculation or a source-level percentile function depending on the field and connector. A table calculation ranks the marks present in the partition. State which population and tie behavior the question requires.

Quick table calculations and customization

The pill menu offers quick calculations such as Running Total, Difference, Percent Difference, Percent of Total, Rank, Percentile, and Moving Calculation. This adds a configured table calculation to the measure. Use Edit Table Calculation to choose relative directions such as Table Across or Pane Down, or use Specific Dimensions for a field-based configuration that is more resilient to rearranging shelves.

When a quick calculation does not exactly match the requirement, edit it or create an explicit calculated field. A moving calculation exposes the aggregation, number of values, direction, and treatment of nulls. A secondary calculation can apply another transformation to the first result, and nested calculated fields can have separate Compute Using configurations through the nested-calculation selector.

Reliable workflow

1. Build and validate the base aggregate without a table calculation.
2. Identify the marks to address and the dimensions that define partitions.
3. Choose the calculation and required order.
4. Configure Compute Using, preferably with Specific Dimensions for complex views.
5. Test the first, middle, and last marks and each partition reset.
6. Add or remove dimensions and verify that the result still means what its label claims.
7. Use a late display filter only when earlier marks must remain available to the calculation.

A correct formula with the wrong addressing direction is still a wrong answer. The partition is the population; addressing is the path through that population.