How to get averages of calculated fields of pivot tables

[Anuru]

Dear Experts,

I have attached here a work sheet that contains a pivot table with calculated fields starting from row 95. I have here two problems;

01. I cannot get the average values of the calculated fields(I have already removed the original total rows, because they are showing wrong averages)
02. I cannot add more calculated fields after column M

Kindly help solve these issues.

Thanks in advance
Regards
Anuruddha

[dflak]

What is it you wish to show the average of?

[Anuru]

Hi..
I need the average of all the columns (calculated fields) below the pivot table. Preferably, with the "unit wise average" if possible. Manual averages are not possible, because pivot table area changes with every selection..

Regards
Anuruddha

[dflak]

In other words, you want the average of the percents for productivity, down time, etc.?

[Anuru]

Yes, exactly..

[dflak]

The issue is with how the pivot table calculates the Total Hours * Total Workers. I calculated this in the pivot table (Hrs x Workers) and as a helper column in the data.

What is happening when you use this in the pivot table is you get Sum(Hours) * Sum(Workers) which is different than Sum (Hours * Workers). As you can see, on the individual line level, both formulas yield the same result. However if you compute the total hours using the pivot table, you get a much larger figure that seems to exceed the sum of its parts.

Productivity (old) is the way you were doing it. Productivity (new) divides the Achieved SAH by the helper column value instead.

Use the helper column value instead of the computed Hours * Workers wherever this term is used in the denominator.

[Anuru]

OK. thanks. So, do I need to use helper columns for all the other calculations of the pivot table??

[Anuru]

In other words, I cannot have average, sum, etc if I use "calculated fields" in a pivot table?