Calculate # of employees per task when an employee is assigned multiple times per task

[WorkHard_]

What would be the correct way to calculate this following scenario:

Task	Employee
1	Employee1
1	Employee2
1	Employee2
1	Employee3
1	Employee4
1	Employee5
2	Employee1
2	Employee2
2	Employee3
2	Employee 4

Notice, one employee is assigned twice which is normal in this dataset.
Using this measure formula I get:

#ofemployees_per_task = CALCULATE(DISTINCTCOUNT(Table1[EmployeeID])/DISTINCTCOUNT(Table1[ProjectID]))

Which is correct.

Task	# of Employees
1	5
2	4

However, when looking at the total, this is not correct:

# of tasks	# of employees
2	5

Instead, the desired result is:

# of tasks	# of employees
2	9

[Ahmedx · Accepted solution]

pls try this

[Ashish_Mathur]

Hi,

I am very confused about what you want.  Anyways, if you want to add the number of employees for both tasks, then try these measures:

Employee count = distinctcount(Table1[EmployeeID])

Measure = SUMX(VALUES(Table1[Task]),[Employee count])

Hope this helps.