If you’re looking for Excel functions in Power BI, you will recognize that much more of them have been transformed to native DAX functions than to native M functions in Power Query. One of the basic statistical functions that hasn’t made it to M is the NORM.DIST function.
Excel NORM.DIST function in Power Query
The Excel NORM.DIST function returns values for the normal distribution. And depending on the values of its 4th parameter (“Cumulative”: false or true), it returns either the values along the line (probability density function) or the area below the line (cumulative distribution function (CDF)).
Normal distribution functions (Source; https://en.wikipedia.org/wiki/Normal_distribution)
The version for the NORM.DIST function in Power Query I’m sharing here uses a very simple approximation for the calculation of the integral for the CDF. So it trades calculation speed for accuracy. Please share your solution of integral calculations in M in the comments, it will be very interesting to see how the M-engine handles these different approaches.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| let | |
| func = (X as number, Mean as number, Standard_dev as number, Cumulative as logical) as number => | |
| if Cumulative = false then | |
| 1 / (Standard_dev * (Number.Sqrt(2 * Number.PI))) | |
| * Number.Power(Number.E, – 0.5 * (Number.Power((X – Mean) / Standard_dev, 2))) | |
| else // Very simple approximation – Source: https://www.hrpub.org/download/20140305/MS7-13401470.pdf | |
| [ | |
| NormZ = Number.Abs(X – Mean) / Standard_dev, | |
| Calc = ( | |
| (1 / Number.Sqrt(2 * Number.PI)) | |
| * ( | |
| Number.Power(Number.E, – (Number.Power(NormZ, 2) / 2)) | |
| / ( | |
| 0.226 | |
| + 0.64 | |
| * NormZ + 0.33 | |
| * Number.Sqrt(Number.Power(NormZ, 2) + 3) | |
| ) | |
| ) | |
| ), | |
| Result = if X < Mean then Calc else 1 – Calc | |
| ][Result], | |
| documentation = [ | |
| Documentation.Name = " Xls.NORMDIST.pq ", | |
| Documentation.Description | |
| = " Returns the normal distribution for the specified mean and standard deviation. ", | |
| Documentation.LongDescription | |
| = " Returns the normal distribution for the specified mean and standard deviation. https://support.microsoft.com/en-us/office/normdist-function-126db625-c53e-4591-9a22-c9ff422d6d58 ", | |
| Documentation.Category = " Xls.Statistical ", | |
| Documentation.Source = " www.TheBIccountant.com ", | |
| Documentation.Version = " 1.0 ", | |
| Documentation.Author = " Imke Feldmann ", | |
| Documentation.Examples = {[Description = " ", Code = " ", Result = " "]} | |
| ] | |
| in | |
| Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation)) |
How to use
The syntax for the NORM.DIST function in Power Query is identical to the Excel syntax:
- X stands for the value for which you want the distribution
- Mean is the arithmetic mean of the distribution
- Standard_dev holds the Standard Deviation of the distibution and
- Cumulative as a true or false selection indicates if area below the normal distribution line up until the X will be returned (cumulative distribution function) or just the point along the line (probability density function)
Use cases
One prominent use case for this function is the Black Scholes calculation of options prices that I will present in my next blogpost.
Enjoy and stay queryious 😉
The Zelen and Severo (1964) formula mentioned in the link is a bit more accurate an probably not much slower. There’s also a tweak to Choudhury approximation that’s marginally better (see here: https://www.ijser.org/researchpaper/Approximations-to-Standard-Normal-Distribution-Function.pdf).
You may see some improvement in your implementation if instead of
Number.Power(Number.E, – (Number.Power(NormZ, 2) / 2))
you write
Number.Exp( – NormZ * NormZ / 2))
Thanks Alexis!
Pingback: Running NORM.DIST in Power BI – Curated SQL
Pingback: Black-Scholes Pricing in Power Query – Curated SQL
Pingback: Black Scholes Option Pricing with Power Query in Power BI –
I was wondering what sort of license this is under? Apache, MIT, etc.
It looks great and I’d love to be able to test it out!
Pingback: Excel NORM.INV function in Power Query and Power BI –