Unable Calculate Utilzation based on Total number of available hour

[harishdangwal]

I am using a employee data set which contain date, user name, task, shift and total time sepnt. I am not able to calcupate the utilization for the task based on the total number of hours available. For example if an user spent 4 hours in a particular task irrespective of days and he/ shet is present for 20 days in a month so the utilization should get calculated as (total number of time spect in that particular tash for the time period/Total number of hours availabe) so it should be 4/(20*9)=4/180=2%

**9 hours available for a day

However in power BI, what i tried, it is claculating the total number of hours available as only the days*9 in which the employee worked on the particular task. Let say if the user spect 4 hours in 5 days so it is calculation as 4/(5*9) which is wrong. 

Please help me. I will highly appreciate it 

Here is the link and screenshot along with teh data table for easy reference and understanding -

https://app.powerbi.com/view?r=eyJrIjoiNTkwNGZkYWEtZWE5MC00NzU0LTgyMTEtNTAxODYxYzBiYWMzIiwidCI6IjA0O...

screenshot

[harshnathani]

Hi @harishdangwal ,

Can you share sample pbix and expected output.

Regards,

HN

[harishdangwal]

https://1drv.ms/f/s!BBjAvNNiJsqVg1RYR8-8Slz3wm_X?e=DoEzR0F4Tk6HjvZDZMghrA&at=9

Please find the link of sample file 

Expected ouit should be.. 

If time specnt by user 1 in a task 'Process 5' is 48 hours then the utilization should get calculated based on total hours availabe. I mean total days he was present multiplied by 9 

so it should be 48.60/189 (as per the scheenshot that I shared initially).  Thanks 

[harishdangwal]

Thanks Brother, but seems some misunderstanding here. 

1) Total available hours are including all tasks i.e Process 1,2,3,4 and 5 which means 21 days for user 1 so 21*9 = 189 hours. 

2) we can not just count the rows as there are duplicate entries for data so number of days can only be calculated using DISTINCTCOUNT. Hope you have more clarity now.

[harshnathani]

Hi @harishdangwal ,

Use this measure for 

Available Hrs = CALCULATE(DISTINCTCOUNT(Sheet1[Date]),ALLEXCEPT(Sheet1,Sheet1[UserName]))*9

Regards,

Harsh Nathani

[harshnathani]

Hi @harishdangwal

Your user1 has spent 13days * 9 hrs for Process 5.  Not sure where did you get 189 from.

So your measure should be

Available Hrs = COUNTROWS(Sheet1)*9

Let me know incase I am missing anything.

Also model your data better.

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!