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.
The actual database is a bit more complex with a bunch of dimension tables and bridge tables to handle many to many relationships, but let's just assume a very basic table, fCompanies:
| Company name | Closed down | Industry |
| ABC | Boondoggles | |
| BCD | Thingamajigs | |
| CDF | Doohickeys | |
| DFG | 2008 | Boondoggles |
| FGH | 2010 | Boondoggles |
| GHI | 2001 | Doohickeys |
| HIJ | 2020 | Boondoggles |
| IJK | 2010 | Thingamajigs |
| JKL | Thingamajigs | |
| KLM | Thingamajigs |
Some sample measures:
Company count:=COUNTROWS(fCompanies)
Closed down company count:=CALCULATE(COUNT(fCompanies[Closed down]),ISNUMBER(fCompanies[Closed down]))
% closed down:=DIVIDE([Closed down company count],[Company count])
So in the above you'd have n = 10, k = 5, p = 0.5. In the case of the Boondoggles industry in specific, you'd have 3 out of 4 companies closed down. The measure I was trying to make was, in a case like this, a right tailed binomial test to give me the significance of cases where many companies ended up closed.
The actual n I have is around 3000 which became a problem when trying for factorials, but sometimes the sliced distributions I want to compare to the overall distribution still end up very small. If you happen to have a binomial solution I'd prefer that, but hypergeometric would also be welcome. Thanks!
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.
- Anonymous6 years agoNot applicable
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.
- Greg_Deckler6 years agoCommunity Champion
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):
- RobG1232 years agoHelper I
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