Average formula in Pivot calculated field
- Thread starter Pinang
- Start date
p45cal
Well-Known Member
Who would have thought that something as simple as your request would prove to be quite hard?
It turns out that according to Microsoft "Custom formulas operate on sum totals, not individual records." which means that the likes of AVERAGE in a formula operates on a single SUM value rather than a number of values, so the average of a single value is that single value!
The simplest solution seems to be to add a column called Count to your source data with just 1s in, extend the source range for the pivot table to include this new column, then tweak the calculated field formula from:
='Onsite Service revenue'/'Onsite Billed FTE'/8/AVERAGE('On Wkg Days')
(by the way, where AVERAGE('On Wkg Days') is the same as 'On Wkg Days' because of the above)
to:
='Onsite Service revenue'/'Onsite Billed FTE'/8/('On Wkg Days'/Count )
See attached.
I suspect the best way of doing this will be using a DAX measure in Power Pivot so better preparing the data for the pivot table. Trouble is, I don't know enough about defining such a measure with the right context to give the answer you require.
The reason I think it'll be better is that my solution will probably break down if you want to get more sophisticated statistics.
It turns out that according to Microsoft "Custom formulas operate on sum totals, not individual records." which means that the likes of AVERAGE in a formula operates on a single SUM value rather than a number of values, so the average of a single value is that single value!
The simplest solution seems to be to add a column called Count to your source data with just 1s in, extend the source range for the pivot table to include this new column, then tweak the calculated field formula from:
='Onsite Service revenue'/'Onsite Billed FTE'/8/AVERAGE('On Wkg Days')
(by the way, where AVERAGE('On Wkg Days') is the same as 'On Wkg Days' because of the above)
to:
='Onsite Service revenue'/'Onsite Billed FTE'/8/('On Wkg Days'/Count )
See attached.
I suspect the best way of doing this will be using a DAX measure in Power Pivot so better preparing the data for the pivot table. Trouble is, I don't know enough about defining such a measure with the right context to give the answer you require.
The reason I think it'll be better is that my solution will probably break down if you want to get more sophisticated statistics.
Last edited: