BINOM.DIST 05-08-2020 10:02 AM - last edited 05-08-2020 10:06 AM

[Greg_Deckler]

In my recent quest to create or catalog as many DAX equivalents for Excel functions, this one plugs the gaps for the Binomial Distribution functions:

• BINOMDIST
• BINOM.DIST
• BINOM.DIST.RANGE
• BINOM.INV

For BINOMDIST and BINOM.DIST mass functions:

BINOM.DIST = DIVIDE(FACT([n]),FACT([n] - [x])*FACT([x])) * POWER([p],[x]) * POWER(1-[p],[n]-[x])

For BINOMDIST and BINOM.DIST cumulative and BINOM.DIST.RANGE:

BINOM.DIST.CUMULATIVE = 
    SUMX(
        ADDCOLUMNS(
            GENERATESERIES(0,[x]), // For range, change 0 and [x] to the range
            "B",DIVIDE(FACT([n]),FACT([n] - [Value])*FACT([Value])) * POWER([p],[Value]) * POWER(1-[p],[n]-[Value])
        ),
        [B]
    )

For BINOM.INV:

BINOM.INV = 
    VAR __Alpha = [Alpha]
    VAR __Table =
        ADDCOLUMNS(
            GENERATESERIES(0,[n]), // For range, change 0 and [x] to the range
            "B",DIVIDE(FACT([n]),FACT([n] - [Value])*FACT([Value])) * POWER([p],[Value]) * POWER(1-[p],[n]-[Value])
        )
    VAR __CumulativeTable = 
        ADDCOLUMNS(
            __Table,
            "Cumulative",SUMX(FILTER(__Table,[Value] <= EARLIER([Value])),[B])
        )
RETURN
    MINX(
        FILTER(
            __CumulativeTable,
            [Cumulative] >= __Alpha
        ),
        [Value]
    )

NOTE: I use the factorial form of the binomial distribution formula because in testing it was more performant than the form using combinations. On average, about 25% less time to return results per the Performance Analyzer. But, for reference, BINOM.DIST1 is the combination form:

BINOM.DIST1 = COMBIN([n],[x]) * POWER([p],[x]) * POWER(1 - [p],[n] - [x])

eyJrIjoiMzc1ODkyY2UtNTZiZi00NTVlLWJjNTQtYTVlMzU3MjZmNTA1IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9