Pivot Table Calculated Field Summing incorrectly

[Bobadavis]

Hi all,

I have a calculated field that sums number of days multiplied by a day rate for several people on a project.
However the sub total in the pivot table is incorrect as the calculation is summing all rows as well as individual rows (hard to explain).

Resource Rate May4 May11 May18 May25
1177.5 1 1 1 1
660 1.5 1 1.5 2

This is the date the calculated field is based on. I want to calculate "May Spend".

The formula in the calculated field is (May4+May11+May18+May25)*Resource Rate. So manually that should be ((1+1+1+1)*1177.5)+((1.5+1+1.5+2)*660))

When viewed in the pivot table the spend per resource is showing correct however if showing sub-totals, the sub-total are all wrong as the calculation is doing this: ((1+1+1+1)*1177.5)+((1.5+1+1.5+2)*660)+((1177.5+660)*(1+1+1+1+1.5+1+1.5+2)))

It's probably a really simple one but it's got me baffled (I'm new to pivot tables and calculated fields).

I have attached the Excel file.

Thanks team!

[PCI]

See here 2 works around:
Draft Pivot1.xlsx where an extra column is added for each month in the database May-Spend1, Jun-Spend1 ... making the calculation for each month

Draft Pivot2.xlsx where 3 extra columns are added and all date's columns deleted:
column Date, Load and column ResLoad to make the calculation Load * Resource Rate => This formula does not work properly neither when recorded in the PT ...!

Note: A possible reason of the issue is that data is not completely in column : Exists a column for each date, but why in Draft Pivot2.xlsx it does not work ...!

[rorya]

FYI, calculated fields always sum their constituent fields before doing anything else, so your formula is actually:

(SUM(May4)+SUM(May11)+SUM(May18)+SUM(May25))*SUM(Resource Rate)

and not what you want, which would be:

SUM((May4+May11+May18+May25)*Resource Rate)