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!
Bookmarks