Adding row with averages to pivot table

[sicilianif]

I have a pivot table that shows the total quantity for the last ten days for each plant. Across the top is the plant, along the side is the 10 days. Each value is the total for that day.

At the end I would like to have a row that shows the avg for the last 10 days. Is something like this possible?

Thanks,

Frank

[Oleg_Troyansky · Accepted solution]

I think you can do it using advanced aggregation. If you modify your Expression along the following lines, and request subtotals for the Day, you should get youat you are looking for:

avg( aggr( sum(Quantity), Plant, Date))

You may also need to condition your formula on the level of detail - only use it when you are showing totals for the Plant. In this case, you'll need to use function Dimensionality():

if(Dimensionality() = 1, // Total by the first dimension
 avg( aggr( sum(Quantity), Plant, Date)),
 sum(Quantity)
)

[stephencredmond]

This might be a little simpler - although AGGR is a great function to use if you need correct sum of rows, etc. in a pivot table.

If you set your Sales calculation as:

Sum(Sales)/Count(Distinct Plant)

Then it will just calculate the correct sum on each Plant row (because the Count of Plant = 1) but will give you an average at the total.

Try it and see.

Stephen

[sicilianif]

That worked. I had started down that path before, but I did not include both dimensions in the aggr statement, which caused me not to get the results that I was looking for.

I did not have to add the condition.

Thanks,

Frank

[sicilianif]

Stephen,

That did not work because the plant is not the row dimension. It is the column.

I tried the same logic with the Date, but could not get it working. I believe it has something to do with the Set Analysis I am doing in the sum.

Thanks,

Frank