Calculating Monthly Variance in Pivot

[Anonymous]

I have a pivot table in which I'm trying to calculate a yearly average in a column, but having the month as a dimension is throwing it off. An idea for the final output would be like the table below. Any thoughts?

Month	Cost	Year Average	Variance
Jan	10	21	-11
Feb	15	21	-6
Mar	20	21	-1
Apr	15	21	-6
May	5	21	-16
Jun	45	21	24
Jul	30	21	9
Aug	20	21	-1
Sep	15	21	-6
Oct	30	21	9
Nov	20	21	-1
Dec	25	21	4

[sunny_talwar]

May be like this:

CostExpression - Avg(TOTAL Aggr(CostExpression, Month))

Where Replace CostExpression with your expression for Cost

[sunny_talwar]

Assuming is it Sum(Cost)

Sum(Cost) - Avg(TOTAL Aggr(Sum(Cost), Month))

[Anonymous]

Thank you! That worked. What if I wanted to run it as a rolling n-period average?

[sunny_talwar]

Can you elaborate with an example?

[Anonymous]

Yes, so in the same table above, if I wanted the December number to the a three-period average, it would calculate the average of December, November, and October. If I wanted the same calculation for August, it would calculate the average for August, July, and June.

[sunny_talwar]

May be like this

CostExpression - RangeAvg(Above(CostExpression, 0, 3))