Forum Discussion

jmg80525's avatar
jmg80525
Icon for Helper II rankHelper II
2 years ago

Group By Standard Deviation not working as expected. Returns 0 for values w/ variance

Lot's of solved threads that suggest using List.StandardDeviation to group value on SD. Seem easy enough. Below is a filterd set of values that clear have variance. 

 

I run Transform>Statistics>Standard Deviation and get the value of 3.26. Seems consistent.

 

 

  I then try 

{"LDSegmentIntID", "TopOfHourCentral"}, {{"SDHrValue", each List.StandardDeviation([LDSegmentIntID]) and I get this:

 

What am I missing? Why woudln't the two values be the same?

Need Help

1 Reply

  • sevenhills's avatar
    sevenhills
    Icon for Super User rankSuper User
    2 years ago

    I think this line of code is wrong...
    {"LDSegmentIntID", "TopOfHourCentral"}, {{"SDHrValue", each List.StandardDeviation([LDSegmentIntID])
    Here, you are grouping by "LDSegmentIntID", "TopOfHourCentral" and trying to get the same standard deviation for the "LDSegmentIntID". Which does not make sense. You need to use the column that you are calculating standard deviation for. 

     

    #"Grouped Rows" = Table.Group(#"Changed Type", {"LDSegmentIntID", "TopOfHourCentral"}, {{"SDHrValue"}, each List.StandardDeviation([?? Value column]), type number}})



    For definition, refer to this: https://learn.microsoft.com/en-us/powerquery-m/list-standarddeviation
    Post the code and sample of data and what you are expecting. (after removing sensitive info).