Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

My formula doesnt work properly

So I have this table

 

VarietySizeMinMaxMax MMMin MMProbA
Grapefruit184.836.06153.924122.6820.993578
Grapefruit234.384.82122.428111.2520.844918
Grapefruit274.234.37110.998107.4420.699794
Grapefruit324.014.22107.188101.8540.422138
Grapefruit363.924.00101.699.5680.311689
Grapefruit403.723.9199.31494.4880.125933
Grapefruit483.593.7194.23491.1860.058042
Grapefruit563.303.5890.93283.820.005854
Grapefruit643.023.2983.56676.7080.000294
Grapefruit640.003.0076.208.36E-41

 

ProbA formula is

ProbA = NORM.DIST('Variety Sizes'[Min MM],[Middle of this Range],[Average Standard Dev],TRUE())
 
but the result are wrong because on excel this is what its showing
 
I think the wrong thing about my formula is this
ProbA = NORM.DIST('Variety Sizes'[Min MM],[Middle of this Range],[Average Standard Dev],TRUE())
 
because i tried this and it works 

Measure = NORM.DIST(111.24800,[Middle of this Range],[Average Standard Dev],TRUE())
 
which match my excel file.
 
can anyone help me? hope this is pretty simple code issue.
 
thanks!
Need Help

3 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous 

    I can't test this, but according to the documentation, the first expression of the NORM.DIST function must be a number value (you are feeding it a column). 
    so try an aggregation function like AVERAGE(Variety sizes[Min MM]) as the first expression:

    NORM.DIST(AVERAGE(Variety sizes[Min MM]), ...etc

  • DataZoe's avatar
    DataZoe
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    Anonymous Have you tried using 

    SELECTEDVALUE('Variety Sizes'[Min MM])?
     
     
  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    Or this.

    Measure = 
NORM.DIST(
    //MAX represents the value of the current row here
    MAX([Min MM]),
    [Middle of this Range],
    [Average Standard Dev],
    TRUE()
)

     

    Best regards,
    Lionel Chen

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