skip to Main Content

Headcount Analysis banner

One of the nice things about the Power Suite (the term I generally use to refer to Power BI, Power Pivot, and Power Query; does anyone else have any good terms?) is that it can easily allow you to replicate things that other systems of yours should do, even when those systems don’t work well. As an example, I had a manager come to me with a headcount analysis question that seemed like it ought to be straightforward, but our payroll system couldn’t do it: How many people do I have scheduled to work on a given hour of a given day? Are there any gaps or dips? And then the big one: How does this scheduling compare to the work my department has to do?

The manager had been trying to do just the headcount bit in Excel, and it worked okay for one hour at a time but was a serious pain to get a good overall picture. Comparing the headcount to work needed was a pipe dream at this point. So, I start working through the scenario and found out one of the main problems is this department has people working one shift on one day, and a different shift on another day. So, Employee 3 might be working 8 AM to 5 PM on Monday, but 4 PM to 8 PM another day, and so on and so forth.

I got similar requests from a few different managers for different purposes, so I dug around online for a bit and found…nothing. My Google-Fu had utterly failed me, and I couldn’t find anyone doing anything like this at the hour level (if anyone has a different way to do this, I’ll be watching the comments!). I let the ideas percolate around for a bit (read: five months) and tried a few approaches, and eventually came up with a solution. I don’t want to admit how many hours it took me, but like many things, once I figured it out I was amazed at how straightforward it was.

Part I: Headcount

The trick to this is realizing that you need an index. Since we’re at the hour level, you need a unique identifier for every hour of every day, or your schedules will be hopelessly snarled. And since the shifts can vary so much, there was no way to get around it but have one row for every hour of every day. I creatively named this the “Day/Hour Index,” because I’m terrible at naming things.

Highlight Employees

The columns highlighted in green are what I gave the manager to fill out. Every hour that someone was working had a 1, and I gave it to them with 0’s in everything (note that lunch breaks are 0’s, they aren’t working then so other coverage is needed!). Not the prettiest thing in the world, but the pretty comes later!

There’s also a really basic table showing what supervisors have what agents, although not all the agents have supervisors (for “reasons,” which I decided was outside my mandate on this one).

image

I’m not going to go through all the steps for pulling it in (though I will provide the data and PBIX at the end for those interested), but the main thing is to Unpivot the schedules, so you go from this:

image

To this:

unpivot schedule - after

You also need to set up a variant of a calendar table that has your day/hour index. I did this by the simple expedient of pulling in the table that had my schedules and removing the employee info:

Calendar table

Set up a basic table linking agents to their supervisors and you get all the data you need to produce this:

Power BI table with supervisors

You can click on the bars for supervisors, days, hours, etc. and have everything else re-orient. You can search for an agent and see when they’re working. And at a quick glance, you see that 7 AM is a big dip.

Part II: Headcount vs. Work

The trick of this is how you define when work needs to be done. That’s a huge process question, and likely won’t be 100% answerable, so I recommend a couple of simplifying assumptions, examples we used are below:

  1. Figure out what you have data for, and work from that.
  2. Assume that work should be started as soon as it becomes available.

Assuming you have something resembling good data, all you need to do to get this to match up is add in a day/hour index associated with whatever date/time you’ve decided to use. The M for that is below Note that the steps are not sequential, I was doing this in the midst of other changes to clean my data up, and also note that we use a day system with Monday as 1 and Sunday as 7:

= Table.AddColumn(#”Changed Type”, “Start Time Hour”, each Time.Hour([Start Time]), Int64.Type)

= Table.AddColumn(#”Reordered Columns”, “Day of Week”, each Date.DayOfWeek([Start Time]), Int64.Type)

= Table.AddColumn(#”Replaced Value”, “Day/Hour Index”, each 1+[Start Time Hour]+([Day of Week]-1)*24)

Once you have your index, relate the table to your Day/Hour calendar table equivalent, and you can get your information (in my case, non-compliance):

updated Power BI with non compliance info

 And then the holy grail, combining it all together!


Power BI with everything

 

 

 

 

 

 

 

 

See how the Tuesday morning hours jump out at you as having work that needs to be done and no one to do it? That’s when you know you’ve done a good analysis!  When the information politely indicates where, exactly, your problem is, and kindly asks for you to fix it so it can go back to looking pretty.

You can download the .zip containing the .pbix and .xlsx to follow along here.

Microsoft’s platform is the world’s most fluid & powerful data toolset.  Get the most out of it.

No one knows the Power BI ecosystem better than the folks who started the whole thing – us.

Let us guide your organization through the change required to become a data-oriented culture while squeezing every last drop* of value out of your Microsoft platform investment.

Matthew Runyon

Matthew Runyon is a Manager of Business Operations at a Fortune 500 company, where he started in regular operations and eventually found his way to the Dark Si-ahem, Process and Data side in 2015. He picked up Power Query/Pivot in desperation as he was the only non-coder on the team, and was delighted to find that M and DAX allow him to automate and improve more than he ever would have believed possible.

This Post Has 9 Comments
  1. I just use PowerBI. PowerBI- Excel if using Excel’s interface, PowerBI-Desktop for… that other thing.

    Then use a specific tool’s name: PowerQuery(old name), PowerPivot, PowerView… PowerMap.

  2. Hi Matthew, amazing work that you show us to Analyze Headcount at the hour level!!!
    Because I mainly use Power Query and Power Pivot in excel and I liked the model you built, I tried to follow your steps in excel but beginning with building the Week Hours table with Power Query and later building the Working Schedule excel table using the Week Hours table that I created previously with Power Query.

    The steps are the following:
    1. Create a small table in excel with one row and one column with the Start Date of the week. That date is passed as parameter to the Week Hours query.
    2. Create a blank query and insert a 7 days / 24 hour List of dates and hours starting from the date passed as parameter.
    3. Add the columns Day Index, Day/Hour Index, Day and Hour to the Week Hour query.
    4. Load the table from the Week Hours query to an excel table and to the Data Model
    5. Add to the loaded Week Hours table in excel the columns for each Employee and the corresponding scheduled hours.
    6. Create a new “Working Schedule” Power Query query From Table with the table that we just loaded to excel and fed with the Employees and Schedule data.
    8. Unpivot the Employee columns.
    7. Load the Working Schedule query to the Data Model.
    8. Create a new Supervisors query and load it to the Data Model.
    9. Create the relationships.
    10. Create a Dashboard with the four charts and insert a Slicer with the Agents.

    Following is the Week Hours query:
    let
    StartDate = Excel.CurrentWorkbook(){[Name=”tblStartDate”]}[Content]{0}[Date],
    Source = List.DateTimes(StartDate, 168, #duration(0,1,0,0)),
    #”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), {“Date”}, null, ExtraValues.Error),
    #”Inserted Day of Week” = Table.AddColumn(#”Converted to Table”, “Day Index”, each Date.DayOfWeek([Date], Day.Monday)+1, Int64.Type),
    #”Added Index” = Table.AddIndexColumn(#”Inserted Day of Week”, “Day/Hour Index”, 1, 1),
    #”Inserted Day Name” = Table.AddColumn(#”Added Index”, “Day”, each Date.DayOfWeekName([Date]), type text),
    #”Inserted Hour” = Table.AddColumn(#”Inserted Day Name”, “Hour”, each Time.Hour([Date]), Int64.Type)
    in
    #”Inserted Hour”

    Here is the link to download the excel file:
    https://drive.google.com/open?id=1ahhtB9QNaUY0MQV8yV_XnH3AK_njpKyH

      1. HI Matthew, read your post on this. Interesting model, and insight. You asked how it can be performed in other ways. There are dedicated solutions for WFM- work force management out there. But most requires that you can enter incoming volumes (of work) in Erlang. Which a mat formula developed in the 1920´s to calculate the load on telephone systems. Reason being that this is used is that most of this type of work would in earlier days require an Agent to logged into a deskphone. This has changed over last 10 years, as other channels are now use and staff often does several types of work. Eg. projects and support work bundled.

  3. Alberto and Marco talked about that sort of a problem in their book «Analyzing Data with Microsoft Power BI and Power Pivot for Excel” in Chapter 7. Analyzing date and time intervals.

    “…In this new model, the fact basically says, “This day, at this hour, the employee worked.” We increased the detail to the lowest granularity. At this point, computing the number of hours worked does not even require that we perform a SUM. In fact, it is enough to count the number of rows in Schedule to obtain the number of worked hours…”

    1. Aha, I knew someone had to have figured this out already! Though I’m pretty pleased with myself if I managed to replicate something they wrote about, as three quarters of the time I can’t even understand the problem they’re trying to solve, much less how they pull it off.

Leave a Comment or Question