Excel pivot table - average of calculated sums

Question

I'm sure this is simple, but how do I get a pivot table to display an average for a calculated sum of fields? In the simplified example, I've filtered out fund x1, and the pivot table is showing the sums of the remaining funds per person. Now how do I get an average by person (so, manually calculated, 3300/3)?

I tried using a calculated field, but cannot figure out how it will work because the denominator will change based on how many people will have the funds I'm filtering on. If I use the averaging inside the calculated field it goes back to averaging the funds.

I tried putting the calculation outside the pivot table, and this works, but of course as I filter, my calculated field is no longer adjacent to the pivot table data, instead just floating off on the worksheet by itself.

TIA.

Per request here is the field list - if I try adding an "average of amount" to the value box it averages the fund amounts, instead of the fund amount per person. :

Answer 1

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.

Answer 2

I would add a helper column in column D to count unique customers.

1. Sort your data by person
2. In column D2 put =IF(A2=A1, 1, 0) and call the column UniqueCust
3. Copy formula down your dataset for all rows
4. Add the column to your pivot
5. Create a formula in your pivot table called Avg per Cust =Value/UniqueCust

This will flag each row in your data with a 1 if it is the first time a name appears in the column or zero otherwise. The pivot table calculation will sum up the total value and divide by the total unique customers.

Answer 3

I'm assuming that you want the value to come in any pivot table and not in a cell calculated outside the pivot table as a formula.

As a workaround you can use another pivot table, which takes the input as the original pivot table to find the average.

pivot tables

The second pivot table has data source as- E3:F5 or till whatever row you require.

You'll have to refresh all so that the second pivot table reflects any changes in the filter of first pivot table.

I've hidden (or you can filter it out) the grand total in the first pivot table so that the average gives the average of the sum of the amounts.

Answer 4

Since you didn't mention how you are using the data, I will give a couple of options that could work.

If you are the only user of the data or if someone else using the data is pivot table savvy you can use the following field list setups to switch data usage:

Sort by Fund (Note the Fund and Person in the Row labels section)

and Sort by Person (Note the reversed position of Fund and Person in the Row labels section)

To simplify the data, you can always minimize the main fields.

If you are distributing the pivot table to other people who aren't able or willing to modify the pivot table data, I would recommend setting up two separate pivot tables (using the same data source) with the same setups that I showed above.

Answer 5

1. Click the pivot table to bring up the field list.
2. In the Values section where it says "Sum of Amount" click the drop down.
3. Click "Value Field Settings" and choose average from the list in the pop-up.