Excel NORM.DIST function in Power Query and Power BI

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.


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))

view raw

Xls.NORMDIST.pq

hosted with ❤ by GitHub

How to use

The syntax for the NORM.DIST function in Power Query is identical to the Excel syntax:

  1. X stands for the value for which you want the distribution
  2. Mean is the arithmetic mean of the distribution
  3. Standard_dev holds the Standard Deviation of the distibution and
  4. 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 😉

Comments (4) Write a comment

  1. 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))

    Reply

  2. Pingback: Running NORM.DIST in Power BI – Curated SQL

  3. Pingback: Black-Scholes Pricing in Power Query – Curated SQL

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz