Eliminate zombie employees

[macfuller]

I am trying to create a simple productivity metric to show how many invoices per AP clerk we process each month. In simplified terms

Invoice Count:= DISTINCTCOUNT([Invoice Number])
FTE Count:= DISTINCTCOUNT([User ID])
Invoice per Clerk:= DIVIDE([Invoice Count], [FTE Count])

But... we have employees who are no longer with us who created recurring payments, so each month the payment appears and the zombie employee gets added to the FTE Count for that month and drags down the invoice per clerk count. I would like to screen out the employees with minimal invoice production on the assumption they're no longer there.

I'm hoping someone has already encountered what must be a somewhat common problem. I'm thinking of something along the lines of

No Zombie FTE :=
VAR ZombieLimit = 10
RETURN
    CALCULATE (
        COUNTROWS ( VALUES ( Vouchers[User ID] ) ),
        [Invoice Count] > ZombieLimit
    )

Assuming this works (?) the hard limit means I'm looking at just one filter (e.g. 10 per month). If I want to filter on a different time frame or type of voucher then my limit will screen more or fewer FTEs than I wanted. So I'm hoping someone out there has a one-size-fits-all measure where they've solved these problems.

 

[Matt Allington]

DAX formulas always depend on the table structure. How about you post a typical sample workbook showing the structure and current formulas.

 

[macfuller]

A reasonable request. I'm not sure how to oblige with an actual workbook. The data is several hundred thousand rows a month with multiple invoice types, some of which are automated and bypass human users entirely and some of which have a user name but only because they manage the data portal, not because they actually touch the invoices. So the existing filters are more complex. The zombie users in question may have touched 300 invoices/month when employed and left behind 5 monthly recurring payments for leases and other known payment amounts. I'm not sure how I could dummy a dataset with just the appropriate elements but I'll see. In the meantime:

The query is against a single table. There is a separate calendar table linked to the invoice date where I group by the month.
Invoices may have multiple lines.
Each line in the invoice gets the same clerk's name when the clerk enters the invoice.
Each invoice line has the same invoice date.
Recurring payments have the same invoice number as the original but an invoice date <x> months into the future.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]No.[/TD]
[TD]Line[/TD]
[TD]User ID[/TD]
[TD]Amount[/TD]
[TD]Source[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/5/19[/TD]
[TD]456[/TD]
[TD]1[/TD]
[TD]JNAD1[/TD]
[TD]$35[/TD]
[TD]EDI[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/5/19[/TD]
[TD]456[/TD]
[TD]2[/TD]
[TD]JNAD1[/TD]
[TD]$21[/TD]
[TD]EDI[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/6/19[/TD]
[TD]678[/TD]
[TD]1[/TD]
[TD]PQED2[/TD]
[TD]$10[/TD]
[TD]Paper[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/2/19[/TD]
[TD]123[/TD]
[TD]1[/TD]
[TD]JNAD1[/TD]
[TD]$33[/TD]
[TD]EDI[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/3/19[/TD]
[TD]723[/TD]
[TD]1[/TD]
[TD]PQED2[/TD]
[TD]$44[/TD]
[TD]Contract[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So in this example PQED2 left the company in May but the $44 recurring contract payment she created is still showing up for June. Overall if PQED2 has fewer than (say) 10 transactions in any given month then I'd like to consider her user ID invalid and drop it from the count of active employees doing more than 10 transactions in any given month, though still have the invoice #723 transaction show up on the top line of the invoice count total.

To keep the math easy let's say from Jan-Apr I had 20 FTEs each doing 300 transactions a month, so my productivity report would show 6,000 / 20 = 300 lines per FTE. In May I lost 4 FTEs but their recurring transactions are still in the file, so my report should show 6,000 / 16 = 375 invoices per FTE but I still show 300 because the zombie 4 user IDs are showing up. If I can filter out PQED2 and the 3 others based on an admittedly arbitrary transaction count floor that would be a start.

I hope that helps but I know it's not a substitute for actual data.
</x>

 

[Matt Allington]

Yes, this helps. Creating sample data, loading into a model, making sure that the sample data is a suitable set to test etc, is all part of the process of solving a problem. These steps often take 90% of the effort of a solution. If you post suitable test workbooks, then it is easier for someone to help.

Having said that, the high level pattern is as follows.

assumptions
1. You should have a lookup table for your staff called Staff
2. You already have a calendar table


The following filter will provide a virtual table containing all staff with more than X invoices in the current month.

FILTER(Staff,[Invoice Count]>4) --this cannot be used stand alone in a measure.

if you nest this inside another measure, you can get what you want.

Invoice Count = DISTINCTCOUNT(Data[Invoice Number])
Number of Staff with More than 4 invoices = CALCULATE(Countrows(Staff),FILTER(Staff,[Invoice Count]>4))
Total value of sales for Staff with More than 4 invoices = CALCULATE(sum(data[value]),FILTER(Staff,[Invoice Count]>4))