pbPassingBI
/
Calculations advanced 11 min

Table calculations

Running totals, moving averages, and the addressing/partitioning model.

What you'll be able to do
  • Distinguish addressing from partitioning
  • Build a running total and a percent-of-total
  • Use INDEX, RANK and WINDOW functions appropriately

Computed on the result, not the data

Table calculations run after the query, on the aggregated result set that's already in the view. That makes them fast and flexible, but it also means they can only see what's in the view — if a value isn't displayed, a table calc can't reference it.

This is the opposite of an LOD, which changes the query itself.

Addressing and partitioning

Every table calculation splits the view's dimensions into two groups.

Partitioning dimensions define the groups the calculation resets within. Addressing dimensions define the direction it moves along.

A running total addressed along Month and partitioned by Region restarts at each region and accumulates through the months. Get these backwards and you get a plausible-looking but wrong chart — which is why the Edit Table Calculation dialog is worth opening rather than trusting "Table (across)".

The common functions

RUNNING_SUM(SUM([Sales])) accumulates. WINDOW_AVG(SUM([Sales]), -2, 0) gives a three-period moving average — current row plus the two before. TOTAL(SUM([Sales])) gives the partition total, which is the denominator for percent of total.

INDEX() returns position within the partition, RANK() ranks by value, FIRST() and LAST() give offsets to the partition boundaries. LOOKUP(SUM([Sales]), -1) fetches the previous value — the basis of period-over-period change.

Quick table calculations

Right-click a measure in the view and pick Quick Table Calculation for the common ones: running total, difference, percent difference, percent of total, rank, percentile, moving average, YTD.

They're a fast starting point, but always open Edit Table Calculation afterwards to check the addressing. The default rarely matches what you want once the view has more than one dimension.

Key points
  • Table calcs run on the aggregated result, so they only see what is in the view
  • Partitioning = where it resets; addressing = the direction it moves
  • Always check addressing after applying a quick table calculation
Check yourself