Calculating ErlangC with Power Query

I was interviewed by large non-profit for a volunteer project and I was asked about how to apply ErlangC in Power BI. I must admit, I’d never heard of it.

I explained that most things that you can do in Excel transfer to Power BI. Even if that was not possible because you wrote custom VBA, there is always Python or R.

After the call, I researched online and found out a lot about it. Erlang C was developed by a Danish mathematician over a century ago. It is a derivative of the Poisson distribution, but there are distinct differences. The major one being that Poisson models the number of events, while Erlang models the time between events. Erlang distribution is helpful for modeling call center and medical staffing requirements, amongst other things.

After doing more research, I pieced together some M code. It takes advantage of nested let statements to define two helper functions. Once you pass in the parameters, it recursively adds agents and calculates whether or not the required service level is met until it reaches the requirement.

The main Power Query function that was the hero of this whole thing was using List.Generate(). If you use a record with it, you can pass in multiple variables. In this case, I passed in the number of agents and the service level and I basically said, “keep generating probable service levels for agents as long as the generated service level is less than what is required.”

Why? Well, that last record was one less than the actual number of agents that meets the service level. So, I could read that last record with the number of agents, add one to that, and generate the service level that actually does meet the target. A little hard to explain, but this was easier for me to do in Power Query than DAX.

Here is the gist.