Pivot View: Running YTD Total Across Columns?

s_daffern · June 6, 2014 1:09PM

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.

Thanks, Steve

paola · June 9, 2014 7:03AM

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.

s_daffern · June 9, 2014 7:30AM

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.

Obvious really. Sorry.

	RunningTotalYTD.iok	20K
	YTD_RunningTotal.JPG	90K

Welcome!

Categories

Ideas Parade

Tagged