Hello there,
I am hoping that I will get some ideas how to resolve the following issues. I have been racking my brains but I am still struggling.
I am looking for the measure to get to the number of hours calculated from the total budget (by period and wbs) and the applicable hourly rate (by period).
In order to better bring across what my issue is, I attached a spreadsheet with the two tables and what exactly they look like.
I have made attempts with DIVIDE, but I always ended up with the error that the hourly rate lack a sum or count or ...
Thank you for any support,
Ralph
Hi Ralph,
In your example file you want Qty. Hours, but I don't see any column labelled 'Hours'. You have Budget and Hourly Rate. I don't know what the Budget column contains. Are these costs, hours, something else?
In the attached file I have added dimension tables for WBS and Date, which are required for the PivotTable you want to build. Maybe that's enough for you to take it from there. If not, please come back with clarification as to how you want to calculate the hourly rate by period (i.e. what fields would I use).
Mynda
Hi Mynda,
Thanks for your reply. Your pivot table has brought me a bit closer to what I want to achieve.
I did not add a separate column for the quantity of hours because I think (I may be wrong) it can be resolved with a measure (Bugdet divided by Hourly Rate).
I have restructured your pivot table a bit and added (in yellow) what I am looking for. I may be wrong in terms of solving this with a measure.
Best,
Ralph
I have been experimenting a little bit and I have come to believe that in a non-pivot environment I would most likely resolve this issue with SUMPRODUCT
Something like =SUMPRODUCT(C5:C12/H5:H12)
C5:C12 = Array 1 in the table Budget
H5: H12 = Array2 in the table Hourly Rate
But I have learned that SUMPRODUCT is not available in PP.
Hi Ralph,
In Power Pivot you can write measures. Please see the attached file to see if that's what you want.
Note: I'm a bit confused because your SUMPRODUCT example above is for all months, but in the file you have it broken down by month.
Mynda
