Excel – How to add a weighted average in the pivot table

I'll post this comment as answer, as I'm confident enough that what I asked is not possible.

I) Couple of similar questions trying to do the same, without success:

• Pivot Calculated formula: SUM(Field1)/AVG(Field2)
• Excel Pivot Table Calculated Field

II) This article: Excel Pivot Table Calculated Field for example lists many restrictions of Calculated Field:

• For calculated fields, the individual amounts in the other fields are summed, and then the calculation is performed on the total amount.
• Calculated field formulas cannot refer to the pivot table totals or subtotals
• Calculated field formulas cannot refer to worksheet cells by address or by name.
• Sum is the only function available for a calculated field.
• Calculated fields are not available in an OLAP-based pivot table.

III) There is tiny limited possibility to use AVERAGE() and similar function for a range of cells, but that applies only if Pivot table doesn't have grouped cells, which allows listing the cells as items in new group (right to "Fileds" listbox in above screenshot) and then user can calculate AVERAGE(), referencing explicitly every item (cell), from Items listbox, as argument. Maybe it's better explained here: Calculate values in a PivotTable report
For my Pivot table it wasn't applicable because my range wasn't small enough, this option to be sane choice.

Here is working solution:

Firstly you should install or enable Power Pivot. Quoting Microsoft:

Power Pivot is an Excel add-in you can use to perform powerful data analysis and create sophisticated data models.

https://support.office.com/en-us/article/Power-Pivot-Powerful-data-analysis-and-data-modeling-in-Excel-a9c2c6e2-cc49-4976-a7d7-40896795d045?ui=en-US&rs=en-US&ad=US

In newer Excel versions Power Pivot is already installed and you can enable it by going to:

File > Options > Advanced > Data > Enable Data Analysis add-ins: Power Pivot, Power View, and Power Map

Alright, so you have Power Pivot now and you can see Power Pivot tab. Please follow the steps below:

1. Select your data and click add to “data model” icon on Power Pivot tab.
2. In Power Pivot window add column which will count distinct number of persons in the data. =DISTINCTCOUNT([person]) name it for example “DistPersNo”. This is crucial step – Power Pivot enables you to count unique values in selected column.
3. Add another column with formula =[amount]/[DistPersNo] name it “PersonAverages”.
4. In Power Pivot window click PivotTable and add new pivot table to your worksheet.
5. In Pivot Table add 'persons' to rows and 'amount' to values. Now, if you add 'PersonAverages' to values (sum of it) and filter out fund 'x1' you will achieve desired result i.e. value of 1100.

Hope that helps.