Standard Deviation Population - Power Pivot (By Group) Measure

[tonyk86]

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! 

[tonyk86]

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?

[jennratten · Accepted solution]

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])

[tonyk86]

Hi,

   I tried the measure and it is returning #NUM! on the column. I'm not sure why this isn't working. Other standard deviation DAX are returning zeros. I really don't know why this is happening?

[tonyk86]

I figured it out! What was happening was that I grouped by the values in Power Query to create the average column. The DAX Standard Deviation measure formula would not work off this column. I created another table with the original values and joined in the "Avevage" column. Then I was able to utilize the STDEVX.P measure in the Pivot Table. Thank you for your help!

[tonyk86]

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? 

[jennratten]

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

[jennratten]

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)?