Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Have you tried : Aggr(Rangeavg(Above( sum({<Month>}Sales),0,RowNo())), Dept,Month) ?
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
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)