Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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:
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