cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I

## 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 I

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 I

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 I

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors