Forum Discussion
Standard Deviation Population - Power Pivot (By Group) Measure
Hi all,
I'm trying to build a model using Power Pivot (data is already loaded into power query). I'm having a difficult time trying to create a power pivot measure that would return the Standard Deviation Population using the Lookup column (since it needs to be grouped) and the Average column. I tried using SRDEVX.P with the nested summerize function but it returns 0. I'm not sure if I entirely understand that formula and that could be the reason why it didn't work
I've tried creating this entirely in Power Query by grouping and using the standard deviation function, however, Power Query only has the Standard Deviation sample function, not the standard deviation population that I actually need. Any help is appreciated. Thanks!
Try using stdevx.s (evaluate for a sample population, not the entire population), and making your column reference fully qualified, which is a best practice. This means when referencing columns by name, you should include the table with it; when you reference a measure, do not include a table name.
stdevx.s('Week 3', 'Week 3'[Average])
7 Replies
Hello - Is your measure calculating on the field in the fact table? Is the lookup column in the visual coming from the dimension table? Is your relationship one (dimension) to many (fact)?
Hi! Basically the table pictured above was from two tables that I loaded in power query. I created the lookup column in Power Query by joining the location and day of week column.
The pivot measure I tried created was calculating the Standard Deviation (Population) from the Averages column (fact table). Hope this makes sense?
Okay, so if you are using a column in your measure (as opposed to another measure) and you want the value returned for a group of the population, then try using STDEV.S. If you want the standard deviation of the actual sales amount, use the actual sales amount column - otherwise you will be getting the standard deviation of the average amounts.
STDEV.S - use a column, return the value for a sample population
STDEV.P - use a column, return the value for the entire population
STDEVX.S - use a measure or expression, return the value for a sample population
STDEVX.P - use a measure or expression, return the value for the entire population
Hi,
The standard deviation still comes back as zero when i tried using any of the listed functions. I'm using the average column (the standard devation column should return the standard deviation population of the Averages column within the grouping of the lookup column). Any idea on what I might be doing wrong?
Try using stdevx.s (evaluate for a sample population, not the entire population), and making your column reference fully qualified, which is a best practice. This means when referencing columns by name, you should include the table with it; when you reference a measure, do not include a table name.
stdevx.s('Week 3', 'Week 3'[Average])