Error when create calculated column (Norm.Dist) in Power BI

[TWMY1014]

Hi Experts,

As per title, below is my code in calculated column:
******

Normalized =

var AvgStartYear = MIN('**DM-InvestabilityIndex**'[Year])

var AvgeEdYear = 2022

var Mean = CALCULATE(AVERAGE('**DM-InvestabilityIndex**'[Value]),ALLEXCEPT('**DM-InvestabilityIndex**','**DM-InvestabilityIndex**'[Type],'**DM-InvestabilityIndex**'[CITY]),AND( '**DM-InvestabilityIndex**'[Year]>=AvgStartYear,'**DM-InvestabilityIndex**'[Year]<=AvgeEdYear))

var SD = CALCULATE(STDEV.S('**DM-InvestabilityIndex**'[Value]),ALLEXCEPT('**DM-InvestabilityIndex**','**DM-InvestabilityIndex**'[Type],'**DM-InvestabilityIndex**'[CITY]),AND('**DM-InvestabilityIndex**'[Year]>=AvgStartYear,'**DM-InvestabilityIndex**'[Year]<=AvgeEdYear))

var ND = NORM.DIST('**DM-InvestabilityIndex**'[Value],Mean,SD,TRUE())*100

return

ND
******

I also attach part of the image of my table (<2000 rows) in below:

I have succsssfully use same DAX in another table but fail in this.

Appreciate for your advise. Thanks a lot.

[v-yalanwu-msft]

Hi, @TWMY1014 ;

Sorry I can't reproduce your formula, can you share the wrong information tip, or a simple file (after removing sensitive information)?

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TWMY1014]

Hi v-yalanwu-msft,

Thanks for your reply. Seems I can't attach the pbix file here so i screen cap for you.  Thanks.

[TWMY1014 · Accepted solution]

HI, 

I finally figure out.  Normal distribution cannot have 0 standard deviation, which I have for some city, i.e. all vlaue in entire time series was the same.  I just add an if function to become 0.00000001 to minimise effect if the result was 0 to SD dax, then it works.