Generating Random Numbers With Triangular Distribution in Power Query
Custom function to generate a column with triangular distribution using M. I have also included a video showing how to create Risk Model in Power BI using distributions
Although normal distributions are well known, in risk modeling triangular distributions are more useful. Normal distributions are symmetric and most risk profiles in real life are not. This is where triangular distributions can come handy. Also, if you are not sure about which distribution to use, triangular distribution can be used as a first guess.
For example, you are in a meeting and you have been asked to create a schedule risk model. You ask the PM to estimate the uncertainty in the task durations for a new project. It will be hard to estimate the duration using mean and standard deviation to define the normal distribution, plus it will be symmetric (same risk of not completing vs. completing completing on time). Instead you could ask the PM, what's your worst estimate, best estimate and most likely estimate. You can define a triangular distribution using these three values. It is more common to define schedules using PERT distribution, which I have covered in DAX here.
#hide-output
//Author: Sandeep Pawar
//Date: Jan 17, 2022
//PawarBI.com
(lower as number, middle as number , upper as number ) as number =>
let
// Parameters
x = middle-lower,
y = upper-lower,
z = upper-middle,
a = 1,
b1 = -2*lower,
b2 = -2*upper,
t = (x/y),
u = Number.Random(),
// Calculation
c1 = Number.Power(lower,2)-(u*x*y),
c2 = Number.Power(upper,2)-((1-u)*y*z),
// Traingle Inverse
t1 = (-1 * b1
+ Number.Sqrt(Number.Power(b1,2)
- (4*a*c1)))/ (2*a),
t2 = (-1 * b2
- Number.Sqrt(Number.Power(b2,2)
- (4*a*c2)))/(2*a),
inv = if u <= t then t1 else t2
in
inv
Steps
Steps are exactly same as the steps shown here, except use the above function. Make sure you have unique numbers on each row, if you do not, you will need to add an index column.
Here is the resulting distribution:
df = pd.read_clipboard().set_index('Column1').drop('Index',axis=1)
df.head(5)
sns.displot(df['Random Number'], rug=True, kde=True);
