Forum Discussion
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 calculated column or measure of a table in the data model. Is this function available to me? (Excel 365 Business Premium / Windows 10 Pro)
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
2 Replies
- OwenAuger
Super User
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
JollyRoger01Helper III
Please note CONFIDENCE.NORM is NOT available in Excel 2016 DAX.