Making calculations based on data appearing on Pivot Table (not its source data)

[MindYourLanguage]

Hello!

I have attached a sample sheet.

In this sheet, you will notice:
- John has purchased breakfast 2 times
- Sarah has purchased lunch 2 times
- Steve has purchased dinner 2 times

I want my pivot table to have an additional column showcasing the "Average cost of each transaction". Is there some function that would allow me to show what the average cost of each individual's meals are? Can this be done without modifying the source table?

I have to perform such a calculation for thousands of cells but everytime I try and add a calculated field with "Total/ID" it returns the calculation using the "ID" from the source data and not the "Count of ID" we see in the pivot table. So for Steve's dinner, it wont calculate "$65/2" it will calculate "$65/A132"

Any help would be very much appreciated!!! This has been bothering me all morning

[JeteMc]

Hello MindYourLanguage and Welcome to Excel Forum.
Try pasting the following into cell E2 on Sheet2 and copying down:

Please Login or Register  to view this content.

Let us know if you have any questions.

[JeteMc]

Apologies, I failed to say that I had changed the layout of the pivot table to tabular and the field setting of Group to repeat item labels.
I have also thought of another possible solution that involves Power Pivot.
The explicit measure (select the data tab and then select manage data model) that calculates the average cost is: Av. Cost:=AVERAGE(Table1[Total])
The measure is formatted as currency.
The GETPIVOTDATA formula is applied to the pivot table on the left and the explicit measure is used in the pivot table on the right of Sheet2.
Let us know if you have any questions.