Eliminate zombie employees

macfuller

Active Member
Joined
Apr 30, 2014
Messages
317
Office Version
  1. 365
Platform
  1. Windows
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
Code:
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.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
DAX formulas always depend on the table structure. How about you post a typical sample workbook showing the structure and current formulas.
 
Upvote 0
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
1/5/194561JNAD1$35EDI
1/5/194562JNAD1$21EDI
1/6/196781PQED2$10Paper
...
6/2/191231JNAD1$33EDI
6/3/197231PQED2$44Contract

<tbody>
</tbody>

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>
 
Last edited:
Upvote 0
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))
 
Upvote 0
Solution

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top