Calculate Staff cost per hour of the day

[Anonymous]

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

[Greg_Deckler · Accepted 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]

@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.

[Greg_Deckler · Accepted solution]

@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

[Westy85]

@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

Service Period = SWITCH (

      TRUE (),

      Hours[Hour] >= 6.00 && Hours[Hour] <= 11.29 , "Breakfast",

     Hours[Hour] >= 11.30 && Hours[Hour] <= 15.59 , "Lunch",

     Hours[Hour] >= 16.00 && Hours[Hour] <= 21.59  , "Dinner",

     Hours[Hour] >= 22.00 || Hours[Hour] <= 2.59  , "Late Night",

     Hours[Hour] >= 3.00 && Hours[Hour] <= 5.59  , "Other"

)

I then used your measure/matrix fix to create a Period Cost

Period Cost =

VAR __table = SUMMARIZE('Hours',[Service Period],"__value",[Hourly Cost])

RETURN

IF(HASONEVALUE(Hours[Service Period]),[Hourly Cost],SUMX(__table,[__value]))

But this doesnt seem to summarise for the period, only for the total

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

[Anonymous]

@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. 

[Greg_Deckler]

@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

[Anonymous]

Hi @Greg_Deckler 

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

[Greg_Deckler · Accepted 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