An argument of function 'NORM.DIST' has the wrong data type or the result is too large or too small

[2019]

I am trying to create a bell curve; at the moment I am keep getting error message

I am trying to create a calculated column the DAX formula I am using is

NORM.DIST(

'RESP POP Varation'[Online POP],

'RESP POP Varation'[Average],

'RESP POP Varation'[Standard Deviation],

FALSE()

)

The Error message is

“An argument of function 'NORM.DIST' has the wrong data type or the result is too large or too small. If the argument is expected to be a date, that date must be between March 1, 1900 and December 31, 9999.”

My desired output graph is:

Please find links to PBIX file and Excel sheet file

[Greg_Deckler · Accepted solution]

Hmm, will have to look a little deeper. If you do this:

Table = GENERATESERIES(0,100,1)

And then this:

Column = NORM.DIST('Table'[Value],AVERAGEX(ALL('Table'[Value]),[Value]),STDEVX.P(ALL('Table'[Value]),[Value]),FALSE())

And you plot [Column] against x, you get this:

[Greg_Deckler]

Your standard deviation is coming back as nan in row context, try:

Standard Deviation = STDEVX.S(ALL('RESP POP Varation'),[Online POP])

@2019 

Also, just curious, when did you joint the community? 🙂

[2019]

@Greg_Deckler 

I have joined back in august 2019 🙂

[Greg_Deckler]

BTW @2019 , the way I found this was that I removed the normal distribution column and created a column:

Column = [Online POP] & "A:" & 'RESP POP Varation'[Average] & "SD" & 'RESP POP Varation'[Standard Deviation]

Just FYI for future troubleshooting.

[2019]

@Greg_Deckler 

I did followed your steps but still i have not got the desired graph

this is what i have at the moment

[Greg_Deckler · Accepted solution]

Hmm, will have to look a little deeper. If you do this:

Table = GENERATESERIES(0,100,1)

And then this:

Column = NORM.DIST('Table'[Value],AVERAGEX(ALL('Table'[Value]),[Value]),STDEVX.P(ALL('Table'[Value]),[Value]),FALSE())

And you plot [Column] against x, you get this:

[areebkanwar]

you used average(ALL...

What if we want the filters or slicer value to impact? how to do then? 

[2019]

Dear@Greg_Deckler 

I tried also your recommendation and this is the output:

I know something is not right.

As I did it in excel sheet this is how I wish to have it:

Here is the excel sheet file

I appreciate your efforts

[Greg_Deckler · Accepted solution]

@2019 - Sorry, just getting back to this now. The last file you sent was very helpful. I am sorry but I am only able to achieve the following result in Power BI.

They key is to use the Average aggregation versus the default Sum aggregation because there are multiple y values for the same x value. Couldn't get the markers, you only get those with Categorical x-axis. Attaching PBIX.

[2019]

Dear @Greg_Deckler 

Can you please upload the file again, when i tried to open it i get error message

"Object reference not set to an instance of an object"

[Greg_Deckler]

I will but it is likely that you are on an older version that is not compatible with my April 2020 version and you will need to upgrade your version. I noticed this when I opened your file, I got the warning that if I saved my work it wouldn't be able to be opened in the authoring version of the Desktop. Sorry, too much time passed between then and now that I forgot.