Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Implementing Erlang C formula in Power BI

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.

 

 

4 Replies

  • aragaocb's avatar
    aragaocb
    Icon for Advocate I rankAdvocate I
    2 years ago

    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:
     
    • Serge_Power_BI's avatar
      Serge_Power_BI
      New Member
      2 years ago

      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]

      )

       

      )