“I Want to Taste You But Your Lips are Venomous, PWAHSAAHHHNNN!!!!”
(Get It? Poisson/Poison? OK, Read on for a Bell Biv Devoe Reference)
Intro from Rob
Um, wow. A few things:
 Brace yourselves for a dose of awesome.
 I don’t understand everything that’s happening in this guest post.
 So if you “get” all of this, fantastic.
 If you don’t, don’t sweat it – just bask in the power of our toolet – it can truly do anything.
 Our new friend Josh is absolutely killing it with his song references.
Take it Away, Josh…
Since taking on a role in Work Force Management about a year ago, I’ve learned one thing: Staffing a call center is expensive. What I mean is: the staffing software, it’s is rather pricey. So much so, that smaller call centers just can’t afford the tools needed to easily create an accurate staffing model.
But as someone raised to the mantra of: “if you are going to do something, do it right” I decided to learn me some DAX. (To be fair though, what my dad really said was: “Aim low, that way no one can tell when you fail.” But for the sake of this post, we’ll go with the first quote. )
Luckily, Rob was nice enough to teach us the core of using complex equations in his Experiments in Linear Regressing, Parts 1 & 2. So we won’t be entirely lost in new territory, it’ll be more of a: “lost with friends and colleagues, ‘Danger Will Robinson’” sort of excursion.
Using RankX and SumX to create a weighted moving average
The staffing model I use relies on a weighted average of the 4 most recent weeks of incoming calls. Often times however, a week’s data may have been inaccurate, causing us to go a week further back.
The way a weighted average works is that each number is multiplied by the given weight and then divided by the the sum of all weights. So the weights 40, 30, 20, and 10 are assigned to the weeks, giving us an average number of calls that is more heavily influenced by the most recent week.
The wrong way to do this:
I include it here because the interactions between the eight weight measures are really, really neat to watch.
When I originally learned Power Pivot I had limited myself to the most recent 8 weeks of data and created a whole mess of measures that would change depending on which weeks were “good”.
I started with a sum of each week’s calls. And then created 8 weight measures that change depending on the previous weights and the sums of calls:
dumbMeasureWeek8Weight:= // switch depending on sum of weights
If( [DumbMeasureWeek8CallsOffered] = 0, 0,
Switch( [DumbMeasureWeek1Weight] + [DumbMeasureWeek2Weight] + [DumbMeasureWeek3Weight] + [DumbMeasureWeek4Weight] + [DumbMeasureWeek5Weight] + [DumbMeasureWeek6Weight] + [DumbMeasureWeek7Weight],
90, 10, 70, 20, 40, 30, 0 ) )
Basically if there were no calls for the week, the weight is 0. But if there were, it sums the earlier weights to determine if it needs to carry a weight and what that weight should be.
It was truly neat (Except that it only applied to the most recent 8 weeks and at 8 iterations of sums and weights it would cap my 8 gigs (or roughly 8 billion bytes) of RAM, lock my machine for 10 minutes, run out of memory and cancel the refresh.)
The ol’ Rank() and Switch()
So I said to my friend Meredith: “Friend Meredith, if I’m gonna do it this with a sumX, I need some way to rank the weeks.” To which he replied: “You mean like the Rank function?”
And all we need for a good rank is date key to add to the rows in our callData table. A concatenated [weekYear] will do the trick.
But wait, there’s a problem. If we do a RankX() of the [weekYear] on the CallData table, we end up “skipping” the data that does not exist!
And here is why:
If we don’t have any calls we don’t have a row to evaluate, simple as that. (well, by “we” I mean most people. I do occasionally have a row that says “0 calls” for reasons that I cannot disclose… or understand. )
Q: So how do we fix this?
A: By evaluating the source of the [weekYear] of course, The Date/Time Dimensions table!
movingWeighted4WeekAverage= // weighing the weeks with RANKX()
SUMX( dateTimeDim,
[callsOffered] * SWITCH(
RANKX( dateTimeDim, dateTimeDim[yearWeek], , 0, dense ),
1, 40, 2, 30, 3, 20, 4, 10, 0 ) ) / 100
With a simple Sum() of the calls we can SumX() the Date / Time Table, rank everything (existent or not), and multiply it by the appropriate weight. Perfect!
Tada! Moving weighted average!
No matter what four weeks you have selected in your pivot table you will be given an accurate average weighted to the most recent data.
Drawbacks:
This only works if:

You did it correctly

You are working with a minimum of 4 weeks of data.
Why? Because a weighted product is divided by the sum of the weights and we have ours hard coded into the average (100). I know there is a way to fix this but that’s a bridge for another day. So for now: 4 weeks or no weeks.
You can’t use this for anything other than a total
If you try to look at this on a week by week basis, the coordinates of each calculated field will only have one week to rank, thus each week will always be ranked as 1.
Now for the spicy stuff
Erlang C in PowerPivot
Erlang C is a formula used to determine the likelihood that a phone call will have to hold while everyone is out to lunch. To do it in excel you need the built in Poisson() function and if you want the really good numbers you’ll need some amount of VBA as well. But we work in PowerPivot, that’s why we’re here, and DAX does not have a Poisson function. <–(sad face).
So, we’ll just have to do it ourselves.
Once again, we give thanks to our friend Rob “Sweetness” Collie for carrying this concept over the goal line. So we’re basically just working on a 2 point conversion…. followed by an offsides kick recovery and maybe a victory dance or two.
The Erlang C Formula:
It looks big and scary BUT, it only has the two variables: A and N, not so scary anymore. But even easier than that is The Erlang C Formula simplified for Excel:
So really, all we really need is a Poisson measure and we should be good to go.
Let’s take a look at Poisson:
Lucky for us, the Sinclair station down the street from me opened up a delicious Greek restaurant last year. So upon seeing this I was easily able to translate it to English.
Simply put, Poisson takes the average (λ) and tells you the likelihood of your desired outcome (k). It’s called Poisson distribution and math people all over the world use it for stuff. Right along with Cumulative Poisson:
Err… we’ll get back to that one.
Anyway, Poisson!
As Bell, Biv, and DeVoe were so fond of telling us: “She’s the only one that can show us the likelihood of random occurrence because, That girl is Poisson.”
Poisson is comprised of two things, Traffic Intensity and Number of agents.
Traffic Intensity

Calls Expected [or: Average of Calls Offered]

+ Time Interval in Seconds

* Call Duration in Seconds
We already have our average so let’s look at our time interval.
Time Interval in Seconds=
=SWITCH( DISTINCTCOUNT( dateTimeDim[minute] ),
1, 900, 2, 1800, 4, 3600, 0 )
It basically counts the number of rows per time period. My Date/Time table is comprised of 4 rows per hour so we can only have 3 possible outcomes: 15 mins would be every row, 30 would be every two rows, and 1 hour would be every 4. Now, we could staff to a 24 hour period but that’s crazy talk.
Call Duration in Seconds
We could take an overall average of the calls answered with a measure but we’ll leave it up to the operator and add a disconnected slicer, table, Harvester MAX() combo and build a table of 10 second increments.
And then wrap it all together into our Traffic Intensity measure.
trafficIntensity=
( [movingWeighted4WeekAverage] + [callIntervalSeconds] )
* [callDurationSeconds]
And voila, nothing difficult has been accomplished…
Number of Agents
We’ll achieve this with another Disconnected slicer, table, MAX() combo but this time we must make sure to start the Agent table at 0 and go up in increments of 1.
“0” You say? Yes, file it under “i” for: Important.
Poisson
On a side note: For the week or so I spent working on this, I survived two nearly fatal attempts on my life for my incessant singing of the lyrics “…your lips, are, venomous, Poisson”. Just be warned, some people don’t like math.
And now were ready to get this show on the road let’s write a Poisson measure!
Earlier I mentioned that this is what we are aiming for:
And we’ve got all that except the ‘e’ and the ‘!’.
Well, the ‘!’ is the factorial of Number of Agents or: Number of Agents multiplied by every number below it in descending order.
And the ‘e’ is 2.718281828459045. Which is really easy to remember. It’s 2.7 1828 1828 45 90 45
Unfortunately for me I spent time irrationally memorizing that number when DAX has a function for it all along, It’s called EXP(). Why? Because Euler. Oh yeah, there’s a function for factorials: FACT().
Poisson=
DIVIDE(
EXP( – [trafficIntensity] )
* POWER( [trafficIntensity], [numberOfAgents] ),
FACTORIAL( [numberOfAgents] ) )
Now we can slap that straight into our measure makin’ device and be off to the races.
Cumulative Poisson
Well, now it’s time for this bugger.
All this means that we need to calculate Poisson for every number that is less than the number of agents on staff (including that ever important 0) and then sum the totals. That’s it, pretty simple except that we can’t do it off of the current Agents table because it has to be filtered by the disconnected slicer in order to return the Number of Agents.
So let’s make a duplicate table and name it something silly.
We’ll need to filter this new table to be < numberOfAgents. So if our numberOfAgents = 10 we will run a SUMX() on a filtered version of our table: rows 0 through 9.
FILTER(
ZAgentsOnStaffCumulative,
ZAgentsOnStaffCumulative[Agents on Staff]
< [numberOfAgents] )
And now we wrap it in the SUMX() and add a Poisson formula that refers to the new table:
PoissonCumulative=
=SUMX(
FILTER( ZAgentsOnStaffCumulative,
ZAgentsOnStaffCumulative1[Agents on Staff]
< [numberOfAgents] ),
DIVIDE(
EXP( – [trafficIntensity] )
* POWER( [trafficIntensity],
ZAgentsOnStaffCumulative[Agents on Staff] ),
FACTORIAL(ZAgentsOnStaffCumulative[Agents on Staff]
) )
It’s like our Poisson but with extra addition.
Erlang C
Alright! Now that we have both Poissons we need to know how long our employees are sleeping each day.
+ [trafficIntensity] / [numberOfAgents]
And now we can throw it all together into our formula.
OR:
Erlang=
+ DIVIDE( [Poisson],
( [Poisson] + (1 – [agentOccupancy] )
* [PoissonC] ) )
And with that treasure just add in a few more useful things:
Target Answer Time: a disconnected slicer, table, MAX() combo for choosing the acceptable hold time.
serviceLevel= //% calls answered within the Target Answer Time
1 – [ErlangC] * EXP(
– ( [numberOfAgents] – [trafficIntensity] )
* [targetAnswerTime] / [callDurationSeconds] )
immediateAnswerPercent= //calls answered without a hold time.
+ ( 1 – [ErlangC] ) * 100
averageSpeedOfAnswer=
+ [ErlangC] * DIVIDE(
[callDurationSeconds],
( [numberOfAgents] * ( 1 – [agentOccupancy] ) ) )
A few percentage conversions ( [measure] * 100 ) and…
TADA!
Every one of those numbers is important for knowing how poorly your employees will do when the calls start arriving.
We are now the best.
Unless…
Solving for x in Erlang C
As helpful as it is to know what percentage of calls will be answered within our target answer time, the real reason we are here is to find out how many agents we need to answer those calls.
But in that lies our problem. If you remember, Erlang C required Number of Agents to get tell us our Service Level, but let’s say we already know what we would like our Service Level to be. What we now need to know is what number of Agents (x) we will need to reach our Service Level
Well, I don’t math very well but PowerPivot does. So after a little bit (read as: about a week’s worth) of thought I decided to see if I could:
Filter a table,
by an equation,
that included a column from the same table.
TryingToFilterForX= //equation with a row from the SUMX table
=SUMX(
FILTER( XAgentsOnStaff,
XAgentsOnStaff[Agents on Staff] * 2 = 6 ),
XAgentsOnStaff[Agents on Staff] )
And it worked! We were able to filter the table to the one row that would equal 6 when multiplied by 2, Perfect!
So that means that we could do something to the effect of:
solvingForAgentsInErlang= //example of what we want to achieve.
SUMX(
FILTER( XAgentsOnStaff,
[ErlangC] >= [desiredServiceLevel] ),
XAgentsOnStaff[Agents on Staff] )
But how do we do that?
Well, we’ll need to replace any, and every instance of our Agents measure with an Agents column reference, that shouldn’t be too hard. Let’s even use a set Desired Service Level of 75 and create a [desiredServiceLevel] once we know it works.
For Example, Let’s open up [serviceLevel]:
serviceLevel=
1 – [ErlangC] * EXP(
– ( [numberOfAgents] – [trafficIntensity] )
* [targetAnswerTime] / [callDurationSeconds] )
We would just change it to be:
serviceLevel=
1 – [ErlangC] * EXP(
– ( XAgentsOnStaff[Agents on Staff] – [trafficIntensity] )
* [targetAnswerTime] / [callDurationSeconds] )
But here’s the catch, none of these measures will work with a column reference so… we’re gonna have to turn them all into one giant formula :/ which, isn’t that bad if you take it a step at a time:
ErlangC=
+ [Poisson] / ( [Poisson]
+ ( 1 – [agentOccupancy] ) * [PoissonC] )
Poisson=
EXP( [trafficIntensity] )
* POWER( [trafficIntensity], XAgentsOnStaff[Agents on Staff] )
/ FACT( XAgentsOnStaff[Agents on Staff] )
agentOccupancy=
+ [trafficIntensity] / XAgentsOnStaff[Agents on Staff]
PoissonC=
SUMX(
FILTER( ZAgentsOnStaffCumulative,
ZAgentsOnStaffCumulative[Agents on Staff]
< XAgentsOnStaff[Agents on Staff] ),
EXP( [trafficIntensity] )
* POWER( [trafficIntensity],
ZAgentsOnStaffCumulative[Agents on Staff] )
/ FACT( ZAgentsOnStaffCumulative[Agents on Staff] ) )
The easiest way to achieve this is to start from the top and replace each measure with the updated code. i.e. where ErlangC requires the [agentOccupancy] measure, replace it with : [trafficIntensity] / XAgentsOnStaff[Agents on Staff]
Now, to save time and space (you can just call me the Dr.) I’ll skip to the finished product.
dontLookAtThisMeasure=
SUMX(
FILTER( XAgentsOnStaff,
+ ( 1 – ( + (
EXP( [trafficIntensity] )
* POWER( [trafficIntensity],
XAgentsOnStaff[Agents on Staff] )
/ FACT( XAgentsOnStaff[Agents on Staff] ) )
/ ( ( EXP( [trafficIntensity] )
* POWER( [trafficIntensity],
XAgentsOnStaff[Agents on Staff] )
/ FACT( XAgentsOnStaff[Agents on Staff] ) )
+ ( 1 – ( + [trafficIntensity]
/ XAgentsOnStaff[Agents on Staff] ) )
* ( SUMX( FILTER( ZAgentsOnStaffCumulative1,
ZAgentsOnStaffCumulative1[Agents on Staff]
< XAgentsOnStaff[Agents on Staff] ),
EXP( [trafficIntensity] )
* POWER( [trafficIntensity],
ZAgentsOnStaffCumulative1[Agents on Staff] )
/ FACT( ZAgentsOnStaffCumulative1[Agents on Staff] )
) ) ) )
* EXP( – ( XAgentsOnStaff[Agents on Staff] –
[trafficIntensity] )
* [targetAnswerTime] / [callDurationSeconds] )
* 100 >= 75 ), 1 ) )
Try not to look directly at it.
Our SUMX() will now return a count of the rows that are >= the number Agents needed, an easy number to work with. Just subtract that from our total number of rows + 1, wrap it in an IF() and we’re good to go.
agentsNeededForServiceLevelPercent=
IF( [movingWeighted4WeekAverage] = 0, 0,
41 – [dontLookAtThisMeasure] )
And there we have it!
Now, Create yourself another: Disconnect slicer, table, MAX() combo for serviceLevelDesired and replace the” >= 75” with something like:
>= IF( [serviceLevelDesired] = 100, 99, [serviceLevelDesired] )
Note: because of the way service level is calculated you can never have a number that is > 100. It’s an impossible equation and PowerPivot will really mess up the numbers trying compute it. Hence the IF() that switches the slicer to 99.
Maybe add a few more slicers to make us feel a little more empowered and…
Done.
I’m going to bed.
You can pick up the work book here for more indepth explanations and examples.
Welcome to the crew, Josh!
Good thing to know that I wasn’t the only one using only Excel to create models . IEX & Blue Pumpkin were cool tho! (back in the day when I used to work for a Call Center 2009ish).
I’ll need to reread this over and over to truly get it while I check the workbook.
My head. It exploded.
Now I’m hungry for Fish ‘n’ Chips
Thanks for sharing that. (I, too, am now in need of some Tylenol.)
Awesome work though!
Now we know why the Poisson (and all the other functions) exists in Excel. I’d go crazy if I had to remember how to rebuild this formula on any type of regular basis. I work with various computer simulation languages, and am reminded (again) of how much I appreciate the builtin functions and statistics.
Wow, this is going to take a while to digest. Nice post!
Does this officially lead all posts on this site when ranked by word count?
great post and awesome use of PowerPivot! Keep it up 🙂
Josh will you please come and work for me? Need a good analyst like yourself.
#Boom