Getting sum of a calculated field in PivotTable

[yellwmonky]

I'm trying to calculate my cost, broken down by where I purchased an item.
In my PivotTable, I have a row for where I purchased the item, broken down by the sale date. The columns are how much I paid per item, how many items i purchased.
I added a calculated field that multiplies cost by number of items purchased.

The totals for the calculated field is going to be incorrect because it simply multiplies the sum of cost by the sum of number of items which is obviously going to be wrong in my case. From what I understand there is no way to change the way Excel calculates this total unfortunately. (Please correct me if I'm wrong)

What I'd like to do is get the sum of all the individual rows in the calculated field column.

I'm hoping to be able to do this using a single PivotTable as the height of the table will all be dynamic as new entries will be made over time. I'd like the PivotTable to update correctly, even when new locations are added. (although this may be getting a little ahead of myself.)

Any help is greatly appreciated. I'm new to PivotTables and I feel what I'm trying to do is fairly straight forward, but can't figure out a good solution.

 

[SullyWYO]

Not sure how your source data is set up, but I got this to work by adding the "Total Cost" calculation to the original data set. I suspect that you are using a calculated field in the Pivot Table, which throws things off in the subtotals.

Just add a "Total Cost" column to the initial dataset (=[@[Amount Paid]]*[@['# Purchased]])

 

[yellwmonky]

Thanks for your response and I see I'm trying to do in the pivot table what I should be doing in the regular table. I will update my table to do the calculations instead of the PT. Appreciate your help!