Forum Discussion

zebra's avatar
zebra
Helper II
2 years ago
Solved

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

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.
 
Need Help
  • Fowmy's avatar
    Fowmy
    2 years ago

    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







7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      zebra
      Helper II
      2 years ago

      Hey Anonymous 

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      zebra
      Helper II
      2 years ago

      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. 

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    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