Implementing Erlang C formula in Power BI

[Anonymous]

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.

[Igor_Sova]

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

[aragaocb]

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)

[Serge_Power_BI]

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]

)

)

[v-shex-msft · Accepted solution]

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