Is it possible to do a running total across a pivot table? I want to aggregate spend across a number of periods along the x axis split by a year in the y axis? The years and periods are dynamic with time i.e. when you move into another quarter you add another period?
So in 2013 quarter 1 we spent £50, in quarter 2 we spent £70 with the period total then being £50 + £70 = £120. I would then compare 2014 quarter 1 against 2013 quarter 1in the pivot.
Apologies if this is simple I just couldn't seem to get it to work using the Running Total formula.
You can add a Running total formula, that runs calculation for each year RUNNINGTOTAL([Spend], SUBSET([Year])) When you aggregate per Quarter (or use Quarter as split option in the Bar/Line view) you should specify 'Last non-empty value' as function. Please see attached demo.
Thanks, this looks to be doing the trick. I was trying to do this as a formula within the measures on the pivot as opposed to a data field. Makes sense.