Creating a Normal Distribution for an ID / Raw Score combination

[MollyWeasley89]

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. 

[v-rongtiep-msft]

Hi  @MollyWeasley89 ,

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.

[MollyWeasley89]

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?