Forum Discussion

Data_Struggles_'s avatar
Data_Struggles_
Frequent Visitor
3 years ago
Solved

M/M/C Queue Theory Formula Implementation

I am attempting to implement an M/M/c Queuing Theory model in PowerBI using DAX(we previously used an Excel Erlang add-in but found it to be inaccurate at low volumes) and I'm running into issues. I ...
DAX
Queue Theory
  • Data_Struggles_'s avatar
    Data_Struggles_
    3 years ago

    As these things tend to go, it turns out I just misunderstood the Summation piece of the Erlang C probability formula. I was able to update my DAX to the following and it worked like a charm:

    QTheoryTest3 = 
var Volume = 50 //in 30 min interval
var IntervalSize = 60 //in minutes
var AHT = 10 //in minutes
var AHT_Adj = AHT / IntervalSize // AHT_Adj is S
var ArrivalRate = Volume * (IntervalSize / 30) // ArrivalRate is lambda
var ServiceRate = (1 / AHT) * IntervalSize
var MinServers = 2
var MaxServers = 50

RETURN
ADDCOLUMNS(
    ADDCOLUMNS(
        ADDCOLUMNS(
            ADDCOLUMNS(
                ADDCOLUMNS(
                    SELECTCOLUMNS(GENERATESERIES(MinServers,MaxServers),"Val1",[Value]), //Val1 is c
                    "Val1-1",[Val1] -1
                ),
                "TrafIntensity", ArrivalRate * AHT_Adj //TrafIntensity is rho
            ),
            //The Summation piece of the Erlang C probability. Val2 is n
            "SubProb", SUMX(SELECTCOLUMNS(GENERATESERIES(0,[Val1-1]),"Val2",[Value]), DIVIDE(POWER([TrafIntensity],[Val2]),FACT([Val2])))
        ),
        "ErlangCProb", DIVIDE(DIVIDE(POWER([TrafIntensity],[Val1]),FACT([Val1])), DIVIDE(POWER([TrafIntensity],[Val1]),FACT([Val1])) + ((1 - DIVIDE([TrafIntensity],[Val1])) * [SubProb]))
    ),
    "AvgWait", DIVIDE([ErlangCProb] * AHT_Adj, [Val1] * (1 - DIVIDE([TrafIntensity],[Val1]))) * IntervalSize //converted back to minutes
)
  • Data_Struggles_'s avatar
    Data_Struggles_
    Frequent Visitor
    3 years ago

    As these things tend to go, it turns out I just misunderstood the Summation piece of the Erlang C probability formula. I was able to update my DAX to the following and it worked like a charm:

    QTheoryTest3 = 
var Volume = 50 //in 30 min interval
var IntervalSize = 60 //in minutes
var AHT = 10 //in minutes
var AHT_Adj = AHT / IntervalSize // AHT_Adj is S
var ArrivalRate = Volume * (IntervalSize / 30) // ArrivalRate is lambda
var ServiceRate = (1 / AHT) * IntervalSize
var MinServers = 2
var MaxServers = 50

RETURN
ADDCOLUMNS(
    ADDCOLUMNS(
        ADDCOLUMNS(
            ADDCOLUMNS(
                ADDCOLUMNS(
                    SELECTCOLUMNS(GENERATESERIES(MinServers,MaxServers),"Val1",[Value]), //Val1 is c
                    "Val1-1",[Val1] -1
                ),
                "TrafIntensity", ArrivalRate * AHT_Adj //TrafIntensity is rho
            ),
            //The Summation piece of the Erlang C probability. Val2 is n
            "SubProb", SUMX(SELECTCOLUMNS(GENERATESERIES(0,[Val1-1]),"Val2",[Value]), DIVIDE(POWER([TrafIntensity],[Val2]),FACT([Val2])))
        ),
        "ErlangCProb", DIVIDE(DIVIDE(POWER([TrafIntensity],[Val1]),FACT([Val1])), DIVIDE(POWER([TrafIntensity],[Val1]),FACT([Val1])) + ((1 - DIVIDE([TrafIntensity],[Val1])) * [SubProb]))
    ),
    "AvgWait", DIVIDE([ErlangCProb] * AHT_Adj, [Val1] * (1 - DIVIDE([TrafIntensity],[Val1]))) * IntervalSize //converted back to minutes
)