Hi! Hoping someone might be willing to help solve this headcount scenario.
I'm trying to calculate distinct headcount for employees with monthly pay >0, but I'm having trouble since transactions are weekly and some of the pays are positive before getting zero'ed out. In the example below, Employee A should not count as a headcount since the sum of their period pay is zero.
I've tried adding summarize to both of the measures below thinking it would aggregate the weekly transactions into monthly totals, but I've not been able to get this to work - either I'm using the function incorrectly, my syntax is incorrect, or both. Possible their is a better solution rather than using summarize.
Headcount:=CALCULATE(
SUMX( DISTINCT(Table1[Name]),1 ),
FILTER(Table1,Table1[Cost Element]=600000 || Table1[Cost Element]=600100),
Table1[Amount]>0)
Headcount2:=CALCULATE(
DISTINCTCOUNT(Table1[Name]),
FILTER(Table1,Table1[Cost Element]=600000 || Table1[Cost Element]=600100),
Table1[Amount]>0)
File is attached and also pics below
I appreciate any help, advice, or guidance.
Kind Regards,
Kevin
Hi Kevin,
How would you calculate that in normal excel table? Hard to see what you mean.
Why the count for employee A should be 0 in both periods 2 and 3?