topic Re: Implementing Erlang C formula in Power BI in DAX Commands and Tips

Implementing Erlang C formula in Power BI

Anonymous — Wed, 14 Sep 2022 06:52:48 GMT

Hi Team,

I am struggling to implement Erlang c formula in Power BI. The requirement needs to sum the series as shown in the denominator.

The value of N also needs to be incremented in order to achieve the optimal solution.

Re: Implementing Erlang C formula in Power BI

Anonymous — Thu, 15 Sep 2022 01:12:13 GMT

HI Anonymous ,

You can take a look at the following link that mentions the Erlang C calculation requirements if it helps:

Analysing Call Center Data - DAX Calculations - Enterprise DNA Forum

Regards,

Xiaoxin Sheng

Re: Implementing Erlang C formula in Power BI

aragaocb — Fri, 10 Nov 2023 01:05:02 GMT

Late flowing, but in case someone else needs this.

I did the below equation with hard coded A and N variables, but you could easily changed that to parameters.

DAX -->

Pw (Probability of Waiting) =
VAR _a = 10
VAR _n = 11
VAR _a2n =
POWER ( _a, _n )
VAR _x =
_a2n / FACT ( _n ) * ( _n / ( _n - _a ) )
VAR _y =
SUMX (
ADDCOLUMNS (
GENERATESERIES ( 0, _n - 1, 1 ),
"current", POWER ( _a, [Value] ) / FACT ( [Value] )
),
[current]
)
VAR _Pw = _x / ( _y + _x )
RETURN
_Pw

--------------------------------------------------------------------------------------------------------------------------------

If you wanted to calculate the service level with DAX, you could do something like this:

SL =
// traffic intensity
VAR _a = 10 // number of agents
VAR _n = 11 // average hold time
VAR _aht = 20 // target answer time
VAR _tat = 180
VAR _result =
1
- (
[Pw]
* EXP ( - ( _n - _a ) * ( _aht / _tat ) )
)
RETURN
_result

-------------------------------------------------------------------------------

If you wanted to do this all in Power Query and calculate how many agents you would need recursively given parameters, you could use this:

Erlang C calculation in Power Query (github.com)

Re: Implementing Erlang C formula in Power BI

Serge_Power_BI — Tue, 18 Jun 2024 20:16:21 GMT

Responding to say thanks as this idea, it helped me build the rest of the formula. I needed a way to get all of the Erlang to be dynamic and output the number of agents required to handle call volume with required SLA. Sharing in case if anyone else has trouble finding a solution in the future. I had to do a recursive series within a series. I'm using Minx() to get the fewest number of agents needed to satisfy the filter criteria (SLA of 80% in this case).

Erlang_Summation_Series_Calc =

SWITCH(TRUE()

,[E_AHT_Seconds]=0,blank()

,[E_AHT_Seconds]=blank(),blank()

,[E_Calls_Per_Hour]=0,blank()

,[E_Calls_Per_Hour]=blank(),blank(),

MINX(

Filter(ADDCOLUMNS (

GENERATESERIES ( 0, 120, 1 ),

"current",

Var E_A2N_T = [E_A_Traffic_Intensity_Erlangs]^[Value]

Var E_Y_T = (

VAR A1 = [E_A_Traffic_Intensity_Erlangs]

VAR N1 = [Value]

RETURN

SUMX (

ADDCOLUMNS (

GENERATESERIES ( 0, N1 - 1, 1 ),

"current", POWER ( A1, [Value] ) / FACT ( [Value] )

[current]

))

VAR E_Probability_of_Waiting_T = (

VAR A = [E_A_Traffic_Intensity_Erlangs]

VAR N = [Value]

VAR X =E_A2N_T / FACT ( N ) * ( N / ( N - A ) )

VAR Y =E_Y_T

RETURN X / ( Y + X )

)

Var Complicated_Calc=-1*([Value]-[E_A_Traffic_Intensity_Erlangs])*([E_Target_Answer_Time_Sec]/[E_AHT_Seconds])

RETURN (1-(E_Probability_of_Waiting_T*EXP(Complicated_Calc)))

),[current]>[E_Required_SLA])

,[Value]

)

Re: Implementing Erlang C formula in Power BI

Igor_Sova — Tue, 30 Jul 2024 07:20:26 GMT

I can recommend the Erlang С functions set in Power Query. Counts for an interval of 1 hour. If you need to take a different interval, then the load should be converted to 1 hour.
https://github.com/IgorSova/ErlangPack