cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

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

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.

1 ACCEPTED SOLUTION
Helper II

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.

3 REPLIES 3
Community Support

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.

Helper II

Hi v-yalanwu-msft,

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

Helper II

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.