Calculate the normal inverse

[DanCasSan]

Hello community!

Again here bothering you with my queries.

I'm trying to calculate the normal inverse, but, I'm getting error. I have tried to do it as a measure and column and in both it throws me a different message. I attached the screenshot so they can guide me what I am doing wrong.

--------------------------------------------------------------------------------------------------------------------------------

Error Message:
MdxScript (Model) (202, 43) Calculation error in measure 'DS_Calculations' [Normal Reverse (k)]: An argument of the function 'NORM.S.INV' has an incorrect data type or the result is too large or too small If the argument is expected to be a date, it must be registered between March 1, 1900 and December 31, 9999.

--------------------------------------------------------------------------------------------------------------------------------

Thank you!

Regards,

[IBIANALYST]

Actually I just found, if you use the formula with SUMX it can work with a measure or a column not only a fixed value.|

Here is the formula I used
Sigma = CALCULATE(sumx([table], -NORM.S.INV([measure])+1.5))

[amitchandak]

Document say it takes Probability. means value between 0 and 1. What values you are passing

NORM.S.INV(Probability)

[DanCasSan]

Hi @amitchandak , thanks for answering my question. I have a % service level column and it contains variant information such as 98%, 96%, 94%, 92% and 90%.

[amitchandak]

You formula return .98 and because you formatted at % it shows 98%. Or is it 98.

If it 98 then divide this column by 100 and use.

98 is one example value, you have lot of such values

[DanCasSan]

It is correct, my data is 0.98, as you can see in the image attached

[dax]

Hi @DanCasSan , 

Based on my test, I find that it seems only work on fixed data like below =NORM.S.INV(0.9), I can't refer to column in this. You also could refer to this video and inverse-normal-cumulative-distribution (this also refer to a cell instead of column) for more details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DanCasSan · Accepted solution]

Correct, apparently only fixed values ​​are allowed, not columns or tables.

But, thanks to your comment I was able to implement an IF to evaluate the field of percentages.
Thank you!

[dax]

Hi @DanCasSan , 

Thank you for your reply! If you'd like to, you could mark corresponding post as answer or share your solutions. That way, people who in this forum and have similar issue will benefit from it.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.