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]

Replace your Dept dimension with Month like in attached sample.

[#3]

Hi Tresco,

Thanks for the reply.

My chart is with Dept,Sales,YTD Sales.

For Dept1 and Jan-16 selection , I want to show YTD Sales 10,

For Dept1 and Feb-16 selection , I want to show YTD Sales 15,

For Dept1 and Mar-16 selection , I want to show YTD Sales 20,

For Dept1 and Apr-16 selection , I want to show YTD Sales 25.

Please help me !

[tresesco]

Jack 061685 wrote:

Hi Tresco,

Thanks for the reply.

My chart is with Dept,Sales,YTD Sales.

For Dept1 and Jan-16 selection , I want to show YTD Sales 10,

And you are not selecting the month to see the result. I am a bit confused.

[#5]

Yes. Selecting the month. But I want to show Dept dimension in the chart/table.

[tresesco]

This is not what you want. Then what do you want it to be like with the same selection?

[#7]

I want to show for Feb-16 selection,

Dept1, Sales 20, YTD Sales 15.

[susovan]

You can try this attachment,

[tresesco · Accepted solution]

Use Exp:

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

[#10]

Hi Susovan,

For Feb-16 your solution showing YTD Avg Sales 20. But it should show 15.