Eliminate zombie employees

macfuller

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

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Matt Allington

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

macfuller

Active Member
Joined
Apr 30, 2014
Messages
257
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:

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,200
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))
 

Forum statistics

Threads
1,089,626
Messages
5,409,399
Members
403,261
Latest member
pcotton

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top