Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
TWMY1014
Resolver I
Resolver 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:
TWMY1014_0-1664430723474.png

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

Appreciate for your advise. Thanks a lot.



1 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.

View solution in original post

3 REPLIES 3
v-yalanwu-msft
Community Support
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.

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_0-1664526267233.png

 

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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.