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.
Hello
I'm trying to calulate the cost per hour for staff to compare to revenue.
I have the timesheet data that shows when they started and finished, along with the costs. i have created a second table that breaks this out into start of hour rows. Example below
What i can't work out how to do is calculate is the cost per hour. So for example I have 2 staff working 16:00 to 17:00 and one 16:00 to 16:30
all on £10 per hour.
cost for this hour should be £25
how to i add the cost for the hour starting at 16:00?
Thanks
Solved! Go to Solution.
@Anonymous I *think* this might be it. See PBIX attached below signature.
Hours =
ADDCOLUMNS(
GENERATE(
SELECTCOLUMNS(
GENERATESERIES(0, 23,1),
"Hour", [Value]
),
SELECTCOLUMNS(
GENERATESERIES(0, 59, 1),
"Minute",[Value]
)
),
"HourMinute", [Hour]/24 + [Minute]/24/60
)
Measure =
VAR __Hour = MAX('Hours'[Hour])
VAR __Table =
SELECTCOLUMNS(
ADDCOLUMNS(
FILTER(
GENERATE(
'Table',
'Hours'
),
[HourMinute] >= [StartTime] && [HourMinute] <= [EndTime]
),
"Cost Per Minute", DIVIDE( [Cost], [Mins] )
),
"Index",[Index],
"Hour",[Hour],
"HourMinute",[HourMinute],
"CostPerMinute",[Cost Per Minute]
)
VAR __Result = SUMX(FILTER(__Table, [Hour] = __Hour),[CostPerMinute])
RETURN
__Result
@Anonymous First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8
@Greg_Deckler thats amazing, thank you so much for doing this!
i have added into my model and it works, thank you. i have a further question, how can i get it to sum the total for the day?
so for example in my model i have sessions where i can total the sales/revenue and i want to then compare the cost per hour.
This is all new to me so your help so far has been invaluable.
@Anonymous First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8
@Greg_Deckler I have done something similar to GordyMac, with the service periods, but am struggling to get the group totals to work.
Firstly I created a column on the hours table using the below
Sorry if this is covered in the links you have posted, I am still very much learning, and have probably missed something obvious.
any assistance is greatly appreciated!
Thanks
@Greg_Deckler Thanks again! i was actually going through the Final Word solution before you posted it as i was searching this morning, so thanks for that post too! You explain the solutions really well.
@Anonymous It's going to be a variation of this the below links (Open Tickets) but based on hour not day. If you can post sample data as text can probably mock it up.
Take a look at these two Quick Measures as I think you want something like them.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365
Thanks for the quick response!
I have added the data in the table below that is the source information, before the extra rows are added in power query.
The examples you showed would be a perfect solution, thanks
Starting | Date | DeptId | EmpId | StartTime | EndTime | Duration | PayId | Mins | Cost | HolidayAccrual |
03-Mar-23 | 03-Mar-23 | 1 | 2511 | 12:00 | 17:00 | 05:00 | 2 | 300 | 47.5 | 5.732758621 |
03-Mar-23 | 03-Mar-23 | 1 | 2928 | 16:01 | 23:22 | 07:21 | 2 | 441 | 69.825 | 8.427155172 |
03-Mar-23 | 03-Mar-23 | 1 | 3982 | 17:00 | 23:15 | 06:15 | 2 | 375 | 46.875 | 5.657327586 |
03-Mar-23 | 03-Mar-23 | 1 | 3987 | 16:00 | 19:16 | 03:16 | 2 | 196 | 20.3186667 | 2.452252874 |
03-Mar-23 | 03-Mar-23 | 1 | 3987 | 19:44 | 21:00 | 01:16 | 2 | 76 | 7.87866667 | 0.950873563 |
03-Mar-23 | 03-Mar-23 | 1 | 4274 | 17:59 | 18:58 | 00:59 | 2 | 59 | 7.375 | 0.890086207 |
03-Mar-23 | 03-Mar-23 | 1 | 110 | 10:00 | 13:05 | 03:05 | 2 | 185 | 29.2916667 | 3.535201149 |
03-Mar-23 | 03-Mar-23 | 1 | 110 | 13:32 | 16:00 | 02:28 | 2 | 148 | 23.4333333 | 2.82816092 |
03-Mar-23 | 03-Mar-23 | 1 | 4270 | 16:00 | 17:58 | 01:58 | 2 | 118 | 14.75 | 1.780172414 |
03-Mar-23 | 03-Mar-23 | 1 | 4270 | 18:26 | 23:15 | 04:49 | 2 | 289 | 36.125 | 4.359913793 |
03-Mar-23 | 03-Mar-23 | 1 | 2063 | 10:00 | 16:00 | 06:00 | 2 | 360 | 57 | 6.879310345 |
03-Mar-23 | 03-Mar-23 | 1 | 4271 | 18:00 | 22:00 | 04:00 | 2 | 240 | 36.72 | 4.431724138 |
03-Mar-23 | 03-Mar-23 | 1 | 4377 | 16:00 | 23:25 | 07:25 | 2 | 445 | 70.4583333 | 8.503591954 |
03-Mar-23 | 03-Mar-23 | 1 | 158 | 13:00 | 21:00 | 08:00 | 2 | 480 | 82 | 9.896551724 |
03-Mar-23 | 03-Mar-23 | 1 | 2444 | 15:33 | 23:30 | 07:57 | 2 | 477 | 81.4875 | 9.834698276 |
03-Mar-23 | 03-Mar-23 | 1 | 110 | 13:05 | 13:32 | 00:27 | 1 | 27 | 4.275 | 0.515948276 |
03-Mar-23 | 03-Mar-23 | 1 | 3987 | 19:16 | 19:44 | 00:28 | 1 | 28 | 2.90266667 | 0.350321839 |
03-Mar-23 | 03-Mar-23 | 1 | 4270 | 17:58 | 18:26 | 00:28 | 1 | 28 | 3.5 | 0.422413793 |
@Anonymous I *think* this might be it. See PBIX attached below signature.
Hours =
ADDCOLUMNS(
GENERATE(
SELECTCOLUMNS(
GENERATESERIES(0, 23,1),
"Hour", [Value]
),
SELECTCOLUMNS(
GENERATESERIES(0, 59, 1),
"Minute",[Value]
)
),
"HourMinute", [Hour]/24 + [Minute]/24/60
)
Measure =
VAR __Hour = MAX('Hours'[Hour])
VAR __Table =
SELECTCOLUMNS(
ADDCOLUMNS(
FILTER(
GENERATE(
'Table',
'Hours'
),
[HourMinute] >= [StartTime] && [HourMinute] <= [EndTime]
),
"Cost Per Minute", DIVIDE( [Cost], [Mins] )
),
"Index",[Index],
"Hour",[Hour],
"HourMinute",[HourMinute],
"CostPerMinute",[Cost Per Minute]
)
VAR __Result = SUMX(FILTER(__Table, [Hour] = __Hour),[CostPerMinute])
RETURN
__Result
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |