Average Sales based on month selection

[#1]

Hi All,

I want to show YTD Sales Average for Dept1  in a pivot table with month selection as shown below:

Month	Sales	YTD Avg Sales
Jan-16	10	10
Feb-16	20	15
Mar-16	30	20
Apr-16	40	25
May-16	50	30
Jun-16	60	35
Jul-16	70	40
Aug-16	80	45
Sep-16	90	50
Oct-16	100	55

Please help me. Thanks in advance.

[tresesco]

Have you tried : Aggr(Rangeavg(Above( sum({<Month>}Sales),0,RowNo())), Dept,Month)  ?

[susovan]

Hi,

But I have checked that the value of YTD Avg Sales are same for both chart.

Please find the below screen shot for the ready reference

[#4]

tresesco,Taking much time to calculate even with less data.Is there another way to do same calculation ?

Aggr(Rangeavg(Above( sum({<Month>}Sales),0,RowNo())),Dept,Month)