Forum Discussion

Regular Visitor

6 years ago

Solved

DAX PowerPivot for Excel - missing NORM.S.INV?

Documentation for DAX refers to the function NORM.S.INV. This function is available to me in 'normal' Excel but I get an error ("unknown function") if I try and use it in a DAX expression in a calcu...

OwenAuger
6 years ago
Hi Dud__099

Unfortunately NORM.S.INV is not available in Excel Power Pivot right now.

However, it turns out you can get the same result using CONFIDENCE.NORM which is available.

Let <Prob> be the probability that you want to pass to NORM.S.INV. Then this DAX expression should return the same as NORM.S.INV would:

NORM.S.INV using CONFIDENCE.NORM = VAR Prob_Diff_From_Half = <Prob> - 0.5 VAR Alpha = 1 - 2 * ABS ( Prob_Diff_From_Half ) VAR Multiplier = SIGN ( Prob_Diff_From_Half ) RETURN Multiplier * IF ( Alpha = 1, 0, CONFIDENCE.NORM ( Alpha, 1, 1 ) )

Kind regards,
Owen

OwenAuger

Super User

6 years ago

Hi Dud__099

Unfortunately NORM.S.INV is not available in Excel Power Pivot right now.

However, it turns out you can get the same result using CONFIDENCE.NORM which is available.

Let <Prob> be the probability that you want to pass to NORM.S.INV. Then this DAX expression should return the same as NORM.S.INV would:

NORM.S.INV using CONFIDENCE.NORM =
VAR Prob_Diff_From_Half =
    <Prob> - 0.5
VAR Alpha =
    1 - 2 * ABS ( Prob_Diff_From_Half )
VAR Multiplier =
    SIGN ( Prob_Diff_From_Half )
RETURN
    Multiplier
        * IF ( Alpha = 1, 0, CONFIDENCE.NORM ( Alpha, 1, 1 ) )

Kind regards,

Owen