Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Hypergeometric or binomial probability density DAX functions?

In Excel there are easy functions for this, like HYPGEOM.DIST and BINOM.DIST, do these exist in DAX in some form?   I tried creating the formulas myself, but because I'm working with rather large n...
  • Greg_Deckler's avatar
    Greg_Deckler
    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.