Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am currently trying to calculate utiliazation of employee per week. In order to do this, I would need a number of employee for any giving week.
I have my timesheet tables set as following
Week Commencing Employee ID Hours
01/01/2018 E-001 3
01/01/2018 E-002 5
01/01/2018 E-001 2
01/01/2018 E-001 2
01/01/2018 E-006 2
01/01/2018 E-001 2
01/01/2018 E-006 2
01/01/2018 E-003 2
01/08/2018 E-003 1
01/15/2018 E-004 1
01/15/2018 E-004 6
01/15/2018 E-001 4
01/15/2018 E-001 3
01/15/2018 E-002 1
01/22/2018 E-002 2
01/22/2018 E-003 2
01/22/2018 E-001 3
And so on. The week keeps on going until the end of 2018. Same employees will show up on same date multiple times, so I need a distict number of employees per week using the ID number.
I would like to see:
Week Commencing Number of Employees Total Number of hours
01/01/18 4 20
01/08/18 1 1
01/15/18 2 14
01/22/18 3 7
Any help would be appreciated. Unfortunately, I am unable to share the data.
Thank you,
Solved! Go to Solution.
Hello,
My apologies, I was able to solve this on my own by just creating a measure for distinct count. Thank you for the Help!
Hi, I have managed to recreate this using similar data - unless I have misundersood your question it should be as simple as creating a table and adding to the Values are the following: Week Commencing, Employee ID and Hours
Then selecting the dropdown arrow on these fields and choosing Count (distinct) for employee ID and Sum for Hours. Hope this helps
Hello,
My apologies for not explaining. Yest this is the value that I am looking for, however, I would like to get cout distinct on DAX so that I can further use the information to calculate other matrix.
Thank you,
Hello,
My apologies, I was able to solve this on my own by just creating a measure for distinct count. Thank you for the Help!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |