Skip to main content

Table Calculation Patterns

Reference library of common table calculations and a debugging table for them.

Common Table Calcs​

// Running total
RUNNING_SUM(SUM([Sales]))
// Addressing: the dimension you're accumulating across (usually date)

// Year-over-Year % change, year axis (one mark per year)
(ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1))
// Addressing: Year. -1 = the previous year. ZN() prevents NULL errors.

// Year-over-Year % change, month axis (one mark per month)
(ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -12)) / ABS(LOOKUP(ZN(SUM([Sales])), -12))
// Addressing: Month of Order Date. -12 = the same month last year.
// With Month addressing, -1 would be month-over-month, not year-over-year.

// Percent of Total
SUM([Sales]) / TOTAL(SUM([Sales]))
// TOTAL() sums across the entire partition

// Rank (Competition)
RANK(SUM([Sales]))

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

// Difference from first value
SUM([Sales]) - LOOKUP(SUM([Sales]), FIRST())
// FIRST() returns the offset to the first row; LOOKUP retrieves that row's value

// Index / Row Number
INDEX()

// Year-to-Date running total that resets each year
// Use RUNNING_SUM with Compute Using = date field, Advanced → Restart every Year
RUNNING_SUM(SUM([Sales]))

Table Calc Debugging​

SymptomLikely CauseFix
All NULLsAddressing dimension not in the viewAdd dimension or change Compute Using
Wrong valuesAddressing/partitioning invertedSwitch Compute Using setting
Doesn't restartPartitioning dimension not setAdd to partitioning or use "Restart every"
Rank ties wrongWrong rank typeRANK() vs. RANK_DENSE() vs. RANK_MODIFIED()
LOOKUP NULL at edgesNo prior/next value existsWrap in ZN() or IFNULL()