cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Data_Struggles_
Frequent Visitor

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 am not a statistician by any means so I've used a GitHub doc as reference(link below; page 150 for M/M/c formulas, 145 for variable info) as I've built the formulas. Below is the DAX script for the table and the link to the reference doc:
Ref doc: https://yzr95924.github.io/pdf/book/Basic-Queueing-Theory.pdf

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])) + DIVIDE(POWER([TrafIntensity],[Val1]),FACT([Val1])))
        ),
        "ErlangCProb", DIVIDE(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
)

  For this test table my desired output is the Average Wait Time (as I'll implement that in a faux for/while loop process), and as I looked through the formulas in the reference doc I found that I largely needed to use the Erlang C probability formula and the expected Wait time formula (both highlighted below) to achieve that. The calculation of these two formulas is what is being performed in my DAX script.

Data_Struggles__0-1677616027937.png

Unfortunately, I am receiving inaccurate results(see screenshot of table below). I have run through the setup compared with the formulas in the reference sheet and for the life of me I can't figure out what is wrong.

Data_Struggles__1-1677616234683.png


As I mentioned above, I'm not much of a statistician and so it is possible I missed something, so I am hoping someone may have some additional insight into where I'm going wrong.

1 ACCEPTED SOLUTION

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
)

View solution in original post

3 REPLIES 3
Data_Struggles_
Frequent Visitor

HI @Greg_Deckler! Yes, I can't attach the pbix directly so here's a OneDrive link to my test pbix:
MMC_Queue_Theory_Test.pbix

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
)
Greg_Deckler
Super User
Super User

@Data_Struggles_ Any chance you can post a link to your PBIX?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors