Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Creating a Normal Distribution for an ID / Raw Score combination

I have a large dataset with about 1500 IDs. Within the same dataset, about 900 / 1500 of these IDs have a Raw Score column valued from 1 - 5 (by 0.1 increments) - Those IDs that don't have a Raw Score are blank in the Raw Score column. I have tried several different ways online to essentialy show my data in each of the 1 - 5 buckets on the x axis and have the SIDs normally distributed for each score. I have not been successful. 

 

One way I have done this is to create a new table ' Normal Distribution' and have the following:

Normal Distribution = GENERATESERIES(1,5,.1) - this is also known as "Value" under the table
F(x) = NORM.DIST('Normal Distribution'[Value],AVERAGE('Normal Distribution'[Value]),STDEV.S('Normal Distribution'[Value]),FALSE())*1000
Z = 'Normal Dist. Monitoring FMI'[Value]-[Mean of FMI Score]/[STD Dev of Monitoring Score]
 
I used a bar graph and put the Value (x) column from the Normal Distribution table on the X axis and F(x) - Normal Distribution on the Y. 
 
I was following an online PBI file that did it this way, as well as a Youtube. However, I now realize - non of those above functions are considering my Raw Score column. I think it is just showing me how numbers 1 -5 would appear normally distibuted because it does not take into my column of interest at all, Raw Score. 
 
As it is now, I have a bar graph that shows the Raw Score values for IDs and places them across the 1 -5 span. I want a graph that shows me the same number of IDs, but this time normally distributed in the 1 - 5 buckets (again, my Raw Score column uses 0.1 increments 1 - 5. 
 
Also, I tried an older video on Youtube that did not use the Norm. Dist function and instead you calculated x+3sigma / x-3sigma variances and used formula - Normal Distribution = VAR MinValue = FLOOR( 0 - 2000, 1 ) VAR MaxValue = CEILING( 0 + 2000, 1 ) RETURN SELECTCOLUMNS( CALENDAR( MinValue, MaxValue), "X", INT( [Date] ) )
 
The problem with this is my Min Value was -0.22. So it was throwing my bar graph out of the 1 - 5 range I wanted. I'll show you the formula I used below
 
Mean of Raw Score = AVERAGE('Data'[RAW Score])
 
STD Dev of Raw Score = STDEV.S('Data'[RAW Score])
X-3a = [Mean]-3*[STD DEV]
 X+3a = [Mean]-3*[STD DEV]
var minvalue - CEILING([x-3a],1)
var maxvalue - CEILING([x+3a],1)
 
I've attached a screenshot of what my key values are as well, STD, Mean, Min, Max - and you can see I have an issue with a negative minimum variance. 
 
General Comment
Need Help
Tips and Tricks

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi  Anonymous ,

    Please have a try.

    Create a measure.

    Normal Distribution Measure = 
VAR MeanRawScore = AVERAGE('Table'[Raw Score])
VAR StdDevRawScore = STDEV.S('Table'[Raw Score])
RETURN
NORM.DIST('Table'[Raw Score], MeanRawScore, StdDevRawScore, FALSE) * 1000

     

    How to Get Your Question Answered Quickly - Microsoft Fabric Community

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

    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

      Okay I have put together the above measure - I just need to know now which components and how to use on the graph to show it. I assume normal distribution measure will be on Y axis. What do I put on X?