Cumulative Binomial Distribution in Power Query

Question

I'm looking to do a cumulative binomial distribution in power query. The data I have is:

1. Employee
2. Audits Completed
3. Failed Audits

I want to know the probability that an employee with less than a 5% audit failure rate is good or lucky. Example, if an employee is audited 20 times with 0 failures, a binomial distribution will say the probability of that with a 5% true failure rate is 36%, so that employee may be lucky. Example 2, for an employee audited 100 times with 1 failure, I would calculate they have 0.6% chance of having 0 failure given a 5% failure rate, and a 3.1% chance of 1 failure, for a 3.7% cumulative chance of having 1 or fewer failures by luck alone.

Answer 1

Here's how I did it.

Starting data:

(please note fail% isn't the employee's fail %, it is the target fail % we are using for the binomial calculation)

M Code:

  #"Added Custom" = Table.AddColumn(#"Changed Type", "s-list", each List.Numbers(0,[fails]+1,1)),
    #"Expanded s-list" = Table.ExpandListColumn(#"Added Custom", "s-list"),
    #"Added Custom1" = Table.AddColumn(#"Expanded s-list", "Binomial Distribution", each Number.Factorial([audits])/(Number.Factorial([audits]-[#"s-list"])*Number.Factorial([#"s-list"]))*Number.Power([#"fail%"],[#"s-list"])*Number.Power(1-[#"fail%"],[audits]-[#"s-list"])),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"ee", "audits", "fails", "fail%", "binom"}, {{"Binomial Distribution", each List.Sum([Binomial Distribution]), type number}})

Explanation

First step uses the List.Numbers function to generate a list from 0 to the total number of failures

Second step expands so each each number in the list is on its own row

Third step adds the non-cumulative binomial probability for each number in the list

Fourth step groups it back up and sums up the binomial distribution for a cumulative distribution