Data Modeling with Timesheets

Hi,

I am trying to model some timesheet data that both looks at the timesheet transactions themselves AND compares and creates metrics using information not in the transactions table.

Ideal Output

StaffName   |  Hours Meant to Have Worked YTD    |   Hours Worked TOTAL YTD |  Invoiced TOTAL YTD |       Invoiced/Hours Meant to have worked YTD

This is for lots of staff.

I have a transaction table that has data such as:

Staff Name | Transaction Date | Hours Worked | Invoiced Value

I then do a CALCULATE(SUM( etc.) to get the hours worked for each staff member and the total invoiced value.  Essentially I can get what I want with the 'history data'.  E.g I can get the Invoiced Total and divide by the Hours Worked Total which gives me the hourly rate for the year.  But what I want to do is calculate the Invoiced Total and divide by the Hours Meant to have Worked Total (e.g. the hours they shoudl have worked - reflects overtime better etc.)

I have created tables with 'dummy' data transactions that allows me to sum the hours meant to have worked for each staff member but I can't get a relationship to work between the tables as they are many:many... I've tried SUMMARIZECOLUMNS to try and get a psuedo pivot table with the dummy data but no luck.

I need help!

Thanks