Data Modeling with Timesheets

[warrenpoh]

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

[v-caliao-msft · Accepted solution]

Hi @warrenpoh，

You can create a table using the DAX below.

Summrize = SUMMARIZE(STALL,STALL[StaffName])

Then add some calculated column like:Hours Worked TOTAL YTD = CALCULATE(SUM(STALL[HoursWorked]),FILTER(ALL(STALL),STALL[StaffName]='Summrize'[StaffName]))Invoiced TOTAL YTD = CALCULATE(SUM(STALL[InvoicedValue]),FILTER(ALL(STALL),STALL[StaffName]='Summrize'[StaffName]))

If this is not what you want, please provide us more information, if possible provide us some cample data and screenshots, so that we can make further analysis.

Regards,

Charlie Liao

[warrenpoh]

Thanks Charlie