Calculated Fields in PivotTables...any trick/workaround to get an "average" to work as the aggregator?

[TomCon]

My understanding of Calculated Fields in PivotTables is that "sum" is used to aggregate a reference to a PivotTable field, and then the formula you define for the field is applied to that sum. I have verified that this behaves as stated.

Does anybody know of a workaround to get a CalculatedField in a PivotTable to use the average? Of course you cannot manually compute that average as SUM/COUNT as you can only use SUM and can't use COUNT, either!

So, lets say Field1 as a calculated field in a PivotTable is defined as:
Field1 = income * 2
where "income" is a field in the PivotTable's data range

If what i want for the result of Field1 is: (AVERAGE of income) * 2 [not: (SUM of income) * 2] ...

anybody know of any tricks or workarounds to get it?

Thanks!

 

[RoryA]

If you have Power Pivot, it’s simple. If not, you can add a counter column to the data that just returns 1 for each applicable row so that you can sum that to get a count. You can then calculate your average using SUM as required by calculated fields.

 

[TomCon]

Thanks...that does work. Appreciate it. Always hate to "mess up the sheet" with dummy type columns, would have been nice to hide it all in a CalculatedField..but oh well, limitations do exist. As an editorial, too bad they did not just make CalcuatedFields have all the same aggregators available that PivotTables have, and allow CalculatedField language to have syntax like SUM(field), AVERAGE(field), Max(field), etc. Do appreciate the post, it will serve my purpose here!