Calculating Normal Distribution based on selected values (slicer/filters)

[zebra]

Hi All,

I am having hard time in calculating dynamic normal distribution based on selected slicer/filter data. Here is the use case, lets say I have people data with age, city and country and I want to calculate the normal distribution for the age column, I am using the following formula to calculate it 

normal_distribution = NORM.DIST(Sheet1[Age],AVERAGE(Sheet1[Age]),STDEV.P(Sheet1[Age]),FALSE())

the formula works fine for all data however if we select data for particular city  (lets say barcelona)that has less range of age, it does not show the correct graph. However When I make the graph only for barcelona dataset, it shows the correct output.

in the following figure, first graph should be similar to the second graph however it is not.

I am attaching the link of pibx file. 

please let me know how to fix it.

[Fowmy · Accepted solution]

@zebra 

Please use following measure:

Norm.Dist = 
VAR __Mean = CALCULATE( AVERAGE(Sheet1[Age]) , ALLSELECTED( Sheet1 ))
VAR __StdDev = CALCULATE( STDEV.S(Sheet1[Age]) , ALLSELECTED( Sheet1 ))
VAR __Result =  NORM.DIST( AVERAGE( Sheet1[Age]) ,__Mean  ,__StdDev,FALSE())
RETURN
   __Result

[Anonymous]

Hi @zebra ,

I understand what you're saying, but there doesn't seem to be a way to resolve that issue for you. It does depend on the data. It's like if you arrange data with a small gap, it will tend to present as a straight line.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[zebra]

well... it does not relly depend on it. this is the generic problem. here the actual problem is how power bi calculate the calculated column and measure. since i have created the calculated column for normal distribution so it is taking the average and standard deviation of whole data whears we need these values for only barcelona data. and the problem is that we can not use measures in calculated columns. this is the actual problem. 

[Anonymous]

Hi @zebra ,

You are correct. Theoretically, all data is capable of using a normal distribution, but set a specific range of labels.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[zebra]

Hey @Anonymous 

does this means that can't we get the desired results?

[Anonymous]

Hi @zebra ,

This is a problem with the data itself, see below:

It suggests that this part of the data is not well suited for a normal distribution

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[zebra]

I think I am unable to explain the problem well. please see the screenshot below

in this screenshot, we have two graphs. i numbered them as 1 or 2. both represents barcelona data.  here I want to have graph 1 similar to graph 2 however this is not despite of the fact that both represent barcelona data. the only difference is that graph 1 is made from that data that consists of all cities data along with barcelona however filtered from top.  please see sheet 1 data. the graph 2 consist of only barcelona data. please see sheet 2 data. 

I hope i clear the problem, here i only want both graphs should be similar. it does not matter that this data is suitable for normal distribtion or not.  please let me know if there is any other ambiguity.