Eliminate zombie employees


Active Member
Apr 30, 2014
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])
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
        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.

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Matt Allington

MrExcel MVP
Dec 18, 2014
DAX formulas always depend on the table structure. How about you post a typical sample workbook showing the structure and current formulas.


Active Member
Apr 30, 2014
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.

DateNo.LineUser IDAmountSource


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.
Last edited:

Matt Allington

MrExcel MVP
Dec 18, 2014
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.

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))

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics