Running totals are one of the most common reporting use cases, but can be tricky when exploring across multiple dimensions. This article presents a quick pattern for running totals inside sub-groups, with and without pivots.
Let’s take a data set with two dimensions, traffic_source and gender, and a single measure, count:
If we naively add a running_total the data will aggregate across both male and female, but we may want to understand the trends inside each.
The simplest option is to pivot our data set and then add a quick calculation for running total:
Be using clever calculations, we can also do the same calculation in the denormalized data set. Here we will use a moving SUMIF to calculate the SUM below a given field. We can subtract this from the total to then calculate a moving average, =SUMIF(B:B, B1, C:C) - SUMIF(B1:B100, B1, C1:C100) + C1. Note how the SUMIF is moving row-by-row in the second image, while the base total remains locked to the full series:
