How to get Weighted Average in PIVOT using (SUMPRODUCT/SUM)

[powerwill]

Office 365 user here.

In the above image you would see, the average that the Pivot derives isn't accurate as the call count isn't the same for all. Hence the Weighted Average derived using (SUMPRODUCT/SUM) is accurate and is different from the Pivot's average. I have 50+ pivots all working together for a Dashboard.

Is there a way to integrate these functions in the PIVOT itself, using calculated field or VBA , so that I dont need to manually calculate the weighted average everytime?

Any help is appreciated!

 

[powerwill]

@AhoyNC do you think if this is even possible? even the MS official site doesn't have any answer.

 

[RoryA]

You'd need to load it into the data model and use measures. A calculated field in a regular pivot always sums the field before doing anything else with it.