Establishing monthly utilisation by employee, project, sector etc

[hsalim]

First of all, a big thank you in dedicating your time to read my post. I’m truly grateful for your help.

I want to create monthly utilisation so it’s possible to see in % terms the total number of hours an employee in an organisation would be working on billable and non-billable projects.

8 hours is the maximum number of working hours per day. And employees are required to enter 40 hours of their time+activity per week.

For example if there are 20 billable days in February and the maximum number of hours is 8 per day. The total is 20 X 8 = 160 hours per month.

Therefore an employee called: ‘Joe’ may have worked 100 hours on a client project which would give us a utilisation of 100/160 – 62.5%

Please see an extract of data below:

Project	Phase	Date	User	Service type	Text	Effort	Rate	Fee
I10ZUK_BD_ADCO	SALES	06/01/2020	adco	100_ADMIN		8.00	0.00	0.00
I10ZUK_BD_ADCO	SALES	07/01/2020	adco	100_ADMIN		8.00	0.00	0.00
I10ZUK_BD_ADCO	SALES	08/01/2020	adco	100_ADMIN		8.00	0.00	0.00
I10ZUK_BD_ADCO	SALES	09/01/2020	adco	100_ADMIN		8.00	0.00	0.00
I10ZUK_BD_ADCO	SALES	10/01/2020	adco	100_ADMIN		8.00	0.00	0.00
I10ZUK_BD_ADCO	SALES	13/01/2020	adco	100_ADMIN		8.00	0.00	0.00
I10ZUK_BD_ADCO	SALES	14/01/2020	adco	100_ADMIN		8.00	0.00	0.00
I10ZUK_BD_ADCO	SALES	15/01/2020	adco	100_ADMIN		8.00	0.00	0.00
I10ZUK_BD_ADCO	SALES	16/01/2020	adco	100_ADMIN		12.00	0.00	0.00
I10ZUK_BD_ADCO	SALES	17/01/2020	adco	100_ADMIN		8.00	0.00	0.00
I10ZUK_BD_ADCO	SALES	20/01/2020	adco	100_ADMIN		8.00	0.00	0.00
I10ZUK_BD_ADCO	SALES	21/01/2020	adco	100_ADMIN		8.00	0.00	0.00
I10ZUK_BD_ADCO	SALES	22/01/2020	adco	100_ADMIN		11.00	0.00	0.00
I10ZUK_BD_ADCO	SALES	23/01/2020	adco	100_ADMIN		8.00	0.00	0.00
I10ZUK_BD_ADCO	SALES	24/01/2020	adco	100_ADMIN		8.00	0.00	0.00
I10ZUK_BD_ADCO	SALES	27/01/2020	adco	100_ADMIN		8.00	0.00	0.00
I10ZUK_BD_ADCO	SALES	28/01/2020	adco	100_ADMIN		8.00	0.00	0.00
I10ZUK_BD_ADCO	SALES	29/01/2020	adco	100_ADMIN		11.00	0.00	0.00

I have a column that already tells me the number of hours he would have worked on a billable or non-billable projects. I don’t have a column where I would be able to divide the hours on billable project over total hours per day (8 hours being maximum)

If I had that extra column, I would be able to work it out but as I’m dealing with a lot of rows, Ideally, I don’t want to be working in Excel and then moving it over to Power BI.

In addition, the total number of hours per day is fixed. 8 hours is the maximum and 160 hours is the maximum hours that will be charged to the client.

I hope that all makes sense, can anyone give me some suggestions / solutions? Thank you

[lbendlin · Accepted solution]

"isn't readily available as a table "  -  then this would be your very first priority. Create a calendar table (outside of Power BI) that has that information. It's not a shame to maintain that table manually.

[hsalim]

Okay, i'll take your word for it, many thanks🙂

[lbendlin]

how do you know how many billable hours / working days are in a month?  Is that information in your calendar table?

What happens when they spend effort across month boundaries? Do you still cut it off at the month?

What happens when they enter nonsensical information like "25 hrs of effort" ?

[hsalim]

To answer your questions:

1.  The total number of billable hours in a day or month isn't readily available as a table which is the challenage that I have right now. I know it's 8 because it's the 'norm'. (Sorry, that, doesn' help, does it?)

2. If an employee has 160 hours in a month, then we're interested to see in what projects those 160 hours have been allocated. What ratio of billable or unbillable would the Employee done? Also, we want to cut it off every month for reporting progress reasons

3. all the data is reviewed by a member of staff so any non-sensical information is dismissed at the early stages.

Does that help?