Forum Discussion
Hypergeometric or binomial probability density DAX functions?
- 6 years ago
OK, the hypergeometric distribution formula I have in my book is hard to write here because there is no formula editor but it goes like this:
P(X=k) = (K k)( N-K n-k) / (N n)
So, (K k) combinations (N-k n-k) combinations, (N n) combinations. So, I assume you have measures or variables for K, k, N and n. The recipe basically goes like below, it is very well explained in the book exactly what is going on:
Probability = VAR __Error = IF(ISBLANK([K Value]) || ISBLANK([k Value 2]) || ISBLANK([N Value]) || ISBLANK([n Value 2]) || [n Value 2]<[k Value 2] || [K Value]<[k Value 2] || [N Value]<[n Value 2] || [N Value]<[K Value], TRUE(), FALSE() ) VAR __Numerator = IF( ISERROR( COMBIN([K Value],[k Value 2]) * COMBIN([N Value]-[K Value],[n Value 2]-[k Value 2]) ), -1, COMBIN([K Value],[k Value 2]) * COMBIN([N Value]-[K Value],[n Value 2]-[k Value 2]) ) VAR __Demoninator = IF( ISERROR(COMBIN([N Value],[n Value 2])), -1, COMBIN([N Value],[n Value 2]) ) RETURN IF( __Error || __Demoninator = -1 || __Numerator = -1, "Bad Parameters", DIVIDE(__Numerator,__Demoninator,0) )There is a LOT of error checking going on here because you can generate a ton of errors caculating out the probability. Otherwise, the basic formula is pretty simple because of the COMBIN DAX function.
OK, the hypergeometric distribution formula I have in my book is hard to write here because there is no formula editor but it goes like this:
P(X=k) = (K k)( N-K n-k) / (N n)
So, (K k) combinations (N-k n-k) combinations, (N n) combinations. So, I assume you have measures or variables for K, k, N and n. The recipe basically goes like below, it is very well explained in the book exactly what is going on:
Probability =
VAR __Error =
IF(ISBLANK([K Value]) ||
ISBLANK([k Value 2]) ||
ISBLANK([N Value]) ||
ISBLANK([n Value 2]) ||
[n Value 2]<[k Value 2] ||
[K Value]<[k Value 2] ||
[N Value]<[n Value 2] ||
[N Value]<[K Value],
TRUE(),
FALSE()
)
VAR __Numerator =
IF(
ISERROR(
COMBIN([K Value],[k Value 2]) *
COMBIN([N Value]-[K Value],[n Value 2]-[k Value 2])
),
-1,
COMBIN([K Value],[k Value 2]) *
COMBIN([N Value]-[K Value],[n Value 2]-[k Value 2])
)
VAR __Demoninator =
IF(
ISERROR(COMBIN([N Value],[n Value 2])),
-1,
COMBIN([N Value],[n Value 2])
)
RETURN
IF(
__Error || __Demoninator = -1 || __Numerator = -1,
"Bad Parameters",
DIVIDE(__Numerator,__Demoninator,0)
)
There is a LOT of error checking going on here because you can generate a ton of errors caculating out the probability. Otherwise, the basic formula is pretty simple because of the COMBIN DAX function.
That's great but I think I need the cumulative hypergeometric distribution to do a Fisher exact test. I can get this in excel from functions. Do you know how to produce that in PowerBI?
Also in your code presumably your notation K and k value 2 is equivalent to K and k (small k)?
Thanks
Rob