counting number of employees by hours worked

[Higgs11]

I have a table that has employee hours by employee ID and Project. In the table the employee id will show up on multiple rows if they work on more than one project. I have a Dax to count the number of employees.

Total Headcount = DISTINCTCOUNT('Employee'[User ID])

I have a DAX for Total Hours

Total Hours = SUM('Employee'[Total Hours])

I have another measure for [Capacity Hours] which is the total hours in the month, for this example it will be 160.

I want to count the number of employees in buckets, for example:

A-Count the # of employees who worked less than 75% of the capacity hours  <= 0.75 *[Capacity Hours]

B-Count the # of employees who worked between 76% and 100% of capacity hours   >=0.75 * [Capacity Hours]

C-Count the # of employees who worked more than 100% of capacity hours  > [Capacity Hours]

The result will give me three rows in a table

Full/Part Time	Total Headcount
A	5
B	9
C	8

Example of 'Employee' table

User ID	Project	Total Hours
a1	aa	100
a1	bb	60
b1	aa	40
c1	bb	160
c1	cc	10
d1	aa	80
d1	cc	20

Thank you

[Jihwan_Kim]

Hi, @Higgs11 

I am not sure if I understood your question correctly, but please check the below picture and the sample pbix file's link down below.

All measures are in the sample pbix file.

Headcount by Group =

COUNTROWS (

FILTER (

VALUES ( Employees[User ID] ),

COUNTROWS (

FILTER (

'Group',

[Total hours Capacity] > 'Group'[Min]

&& [Total hours Capacity] <= 'Group'[Max]

)

) > 0

)

)

https://www.dropbox.com/s/19v1t2k193mqhep/higgs.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Higgs11]

Thank you for responding. I have three DAX Measures, one for Total Headcount, one for Total Hours, and one for capacity/budget hours. The main Employee table is large with Employee ID in multiple rows. I cannot count rows, I cannot "SUMMARIZETABE". I want to know how many employees are within 0-50%. 51%-75%, 76%-100%+ of the capacity/budgeted hours. Three DAX measures. 

I like the Group table, played around with it yesturday. Can I build a group table to compare Total Hours againg Capacity/Budgeted hours? Like 15 Employees worked 25% of the capacity/budgeted time? 

Thanks again 

[Jihwan_Kim]

Hi, @Higgs11 

Thank you for your feedback.

Sorry that I quite do not understand your question.

What do you mean by,  I cannot count rows, I cannot "SUMMARIZETABE" ?

Please share your sample pbix file's link, then I can try to look into it.

Thanks.