Results 1 to 4 of 4

Thread: Eliminate zombie employees
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2014
    Location
    Mitten State
    Posts
    222
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Eliminate zombie employees

    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.

  2. #2
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Eliminate zombie employees

    DAX formulas always depend on the table structure. How about you post a typical sample workbook showing the structure and current formulas.
    Matt Allington is a professional Power BI Consultant, Power BI Trainer and Microsoft MVP based in Sydney Australia. Matt is author of the Power BI book Supercharge Power BI and the Power Pivot Book Supercharge Excel.
    What is Power BI?

  3. #3
    Board Regular
    Join Date
    Apr 2014
    Location
    Mitten State
    Posts
    222
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Eliminate zombie employees

    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 months into the future.

    Date No. Line User ID Amount Source
    1/5/19 456 1 JNAD1 $35 EDI
    1/5/19 456 2 JNAD1 $21 EDI
    1/6/19 678 1 PQED2 $10 Paper
    ...
    6/2/19 123 1 JNAD1 $33 EDI
    6/3/19 723 1 PQED2 $44 Contract

    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 by macfuller; Jun 18th, 2019 at 06:22 AM.

  4. #4
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Eliminate zombie employees

    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))
    Matt Allington is a professional Power BI Consultant, Power BI Trainer and Microsoft MVP based in Sydney Australia. Matt is author of the Power BI book Supercharge Power BI and the Power Pivot Book Supercharge Excel.
    What is Power BI?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •