Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
May be like this:
CostExpression - Avg(TOTAL Aggr(CostExpression, Month))
Where Replace CostExpression with your expression for Cost
Assuming is it Sum(Cost)
Sum(Cost) - Avg(TOTAL Aggr(Sum(Cost), Month))
Thank you! That worked. What if I wanted to run it as a rolling n-period average?
Can you elaborate with an example?
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.
May be like this
CostExpression - RangeAvg(Above(CostExpression, 0, 3))