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

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