Avg is Pivot Table

[#1]

Hi,

I have 3 dimensions in a pivot table, with Qty as measure. I am sowing sum(Qty) for last 3 months by country and category. Now i would like show avg(Qty) for last 3 months by Country anad Category. I can create two separte tables, and show the last 3 months Qty and avg. But am not able to do in a single table. So the question is, can we do avg by different dimensions instead of sum in a pivot table.

I have attached my QV and required view for reference.

Thanks

[Anonymous]

Hi Simky,

Use rolling n-period,Check below link to calculate 3 months avg for qty Calculating rolling n-period totals, averages or other aggregations

[#3 · Accepted solution]

Hey Simky,

Attached with the answer you are looking for.

Thanks

AJ

[#4]

Hi Ajay,

Thanks for the direction. I made slight changes, since the dashboard would be in server with automatic update, so I cant put hardcore monthname.

Max Month Expression :

  =sum( {$<[Month_ID]= {$(vMaxMonthID)}>} Qty )

Similarly Prev Month Expresssion:

=sum({$<[Month_ID]= {$(vMaxMonthID_1)}>} Qty)

where vMaxMonthID=max(Month_ID)  and vMaxMonthID_1=max(Month_ID) -1

Thanks again for your help.

[#5]

Absolutely.

I just did it as an example. Never hard code numbers/texts that will change over time..

The way you did it by using variables is the best approach.

Thanks

AJ