Forum

Headcount by month ...
 
Notifications
Clear all

Headcount by month with pay >0, but weekly transaction noise

2 Posts
2 Users
0 Reactions
60 Views
(@risk)
Posts: 1
New Member
Topic starter
 

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

img1

img2

 
Posted : 26/03/2021 9:15 pm
(@catalinb)
Posts: 1937
Member Admin
 

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?

 
Posted : 06/04/2021 12:49 pm
Share: