Inverse value for Poisson distribution

[chutli]

Has anyone done anything to calculate the Inverse value for Poission distribution or Gamma? 

Similar to the NORM.S.INV() function but for Poission instead of Normal Distribution. 

I am trying to calculate safety stock and setting service level

[v-kkf-msft · Accepted solution]

Hi @chutli ,

The current version does not include this function, please submit your idea.

Ideas (powerbi.com) 

1. Try to use R script, the qpois( ) function will realize it.

2. Or you can try to calculate the result in Excel and then import it into Power BI Desktop. Refer to the code:

Function PoissonInv(dVal As Double, dMean As Double) As Variant
    ' shg 2011
 
    ' For a Poisson process with mean dMean, _
      ' returns the largest integer such that the CDF <= dVal
 
    ' E.g., =POISSON(5, 10, TRUE) returns 0.0670859629
    ' PoissonInv(0.0670859629, 10) returns 5
 
    Dim iX          As Long
    Dim dCDF        As Double
 
    ' these variables are used to simplify this summation:
    '    dCDF = dCDF + Exp(-dMean) * dMean ^ iX / .Fact(iX)
    Dim dExpMean    As Double   ' =Exp(-dMean)
    Dim dFact       As Double   ' incremental factorial
    Dim dPowr       As Double   ' incremental power
 
    If dVal < 0 Or dVal >= 1 Then
        PoissonInv = CVErr(xlErrValue)
 
    ElseIf dVal > 0 Then
        dExpMean = Exp(-dMean)
        dFact = 1
        dPowr = 1
 
        Do While dCDF < dVal
            dCDF = dCDF + dExpMean * dPowr / dFact
            iX = iX + 1
            dFact = dFact * iX
            dPowr = dPowr * dMean
        Loop
 
        PoissonInv = iX - IIf(dCDF / dVal > 1.000000000001, 2, 1)
    End If
End Function

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.