Are my people working when the work needs to be done? Headcount Analysis with Power BI

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.

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

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:

To this:

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:

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

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

 And then the holy grail, combining it all together!

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.