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.
Thanks! That wasn't entirely what I was struggling with but it does help. One of my problems is large K and N values, so COMBIN can't compute them.
But I've also figured out a statistics solution to my large K problem. It's possible to calculate z-scores without involving the binomial coeffecient that requires factorials or COMBIN, and then turn those z-scores into p-values through NORM.S.DIST. I still need to figure out how to translate that into DAX, but it should theoretically work.
Funny, Anonymous , the very next recipe in that chapter is called "Determining the required sample size" and it deals with zscores. You may find this file handy (attached below):