Triangular Distribution

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.


Custom Function

//Author: Sandeep Pawar
//Date: Jan 17, 2022

(lower as number,  middle as number , upper as number ) as number => 
    // 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



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)
Random Number
1 22.048133
2 40.940600
3 46.184121
4 59.807112
5 52.808201
sns.displot(df['Random Number'], rug=True, kde=True);

Case Study: Risk Modeling in Power BI

I gave this presentation at Scottish Summit in 2021. If you are new to risk modeling, uncertainty estimation etc, please watch this video. In this video, I show the DAX version, but you could use either DAX or M to generate the random numbers.