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

[Dud__099]

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 calculated column or measure of a table in the data model.  Is this function available to me?  (Excel 365 Business Premium / Windows 10 Pro)

[OwenAuger · Accepted solution]

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

[JollyRoger01]

Please note CONFIDENCE.NORM is NOT available in Excel 2016 DAX.