14 Replies Latest reply on Feb 26, 2016 4:22 AM by Sriram Natesan

# Calculation -  Normal Distribution

Hi All,

How do I arrive do the following excel calculation in tableau

=NORM.DIST(ABS(G12),0,1,TRUE)

What is the Normal distribution function in tableau

Thanks

• ###### 2. Re: Calculation -  Normal Distribution

Yes I tried but running into difficulties framing a calculation.

Thanks

• ###### 3. Re: Calculation -  Normal Distribution

Gallop,

http://breaking-bi.blogspot.com/2013/03/conducting-2-sample-z-test-in-tableau.html

With your idea and a renewed passion.  I was able to create the attached workbook.

Let me know if this helps,

Associate Consultant

Mariner, LLC

http://breaking-bi.blogspot.com

• ###### 4. Re: Calculation -  Normal Distribution

Nice work Brad - your contribution to the forums has been phenomenal. Have you added any of these to the tableau calculation reference library?

• ###### 5. Re: Calculation -  Normal Distribution

Alex,

If one were so inclined, where would one find such a magical place?

• ###### 6. Re: Calculation -  Normal Distribution

However I have the z-score formula to be:

((Calc)-LOOKUP([Calc], -1))/POWER((((([Calc])*(1-[Calc]))))/SUM([matched])+(((LOOKUP([Calc], -1))*(1 - LOOKUP([Calc], -1)))/(LOOKUP(SUM([matched]), -1 ))),0.5)

And I use the following formulae for p -value

IF INDEX() <> 3 AND INDEX() <> 1

THEN

IF ABS([Z-ScoreCTR] ) <= 2.2 THEN ( .5 - ( ABS( [Z-ScoreCTR] ) * (4.4 - ABS( [Z-ScoreCTR] ) ) / 10 ) ) * 2

ELSEIF ABS( [Z-ScoreCTR] ) <= 2.6 THEN .02

ELSE 0

END END

I have value populated for the z-score but yet the value for p-value is shown as 0.00% . Trying to recify it but though could use an expert help to find reason I am unable to see the value for p-value

Thanks

• ###### 7. Re: Calculation -  Normal Distribution

Gallop,

Could you post your .twbx or a screenshot?

Thanks,

Associate Consultant

Mariner, LLC

http://breaking-bi.blogspot.com

• ###### 8. Re: Calculation -  Normal Distribution

Attached the screen in same message.

Thanks

• ###### 9. Re: Calculation -  Normal Distribution

Gallop,

Look back at your P-Value formula, you are telling it to return zero for Z-Scores more extreme than 2.6.  Also, mathematically, any Z-Score more extreme than 3 has such as an astronomically small probability, it is commonly called zero.  This is all as it should be.  Your report looks great!

Thanks,

Associate Consultant

Mariner, LLC

http://breaking-bi.blogspot.com

• ###### 10. Re: Calculation -  Normal Distribution

Thanks a lot for your excellent response.

I have attached a screenshot from excel. Still how do I arrive at p-value based on the formula attached previously ?

Thanks

• ###### 11. Re: Calculation -  Normal Distribution

Gallop,

Excuse me for getting a little technical here.

A P-Value is

2 * Prob( X > abs( [Z-Score] ) )

In layman's terms, this means "The theoretical probability of observing an outcome at least as extreme as the one actually observed."

What Excel is calculating is

Normal CDF = Prob( X < [Z-Score] )

Therefore, the formula you were using in Excel is incorrect.  Your Excel formula should be

= 2 * (1 - NORM.DIST(ABS(G12),0,1,TRUE) )

I apologize for not recognizing this earlier.

Hope this helps,

Associate Consultant

Mariner, LLC

http://breaking-bi.blogspot.com

• ###### 12. Re: Calculation -  Normal Distribution

http://community.tableau.com/community/calculation-reference-library

If you go to ideas, it is then one of the listed headings on the right. Why it isn't linked from all forum sections I don't know (Dustin Smith )

• ###### 13. Re: Calculation -  Normal Distribution

Excellent solution....Thanks a ton Brad...awesome calculation !!!

• ###### 14. Re: Calculation -  Normal Distribution

Its an interesting solution. I have gone through the other blog also. I would need a small clarification in the calculation where you have used 2.2 in the IF construct and finally divided by 10.

I would like to know whats the baseline behind this formula you have used.

I came across another blog where the CDF is calculated. That also seems interesting just pasting the link here

IF INDEX() <> 3 AND INDEX() <> 1

THEN

IF ABS([Z-ScoreCTR] ) <= 2.2 THEN ( .5 - ( ABS( [Z-ScoreCTR] ) * (4.4 - ABS( [Z-ScoreCTR] ) ) / 10 ) ) * 2

ELSEIF ABS( [Z-ScoreCTR] ) <= 2.6 THEN .02

ELSE 0

END END