Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Thanks Charlie
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
79 | |
74 | |
56 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |