Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tonyk86
Helper I
Helper I

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! 

standard deviation.png

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

View solution in original post

7 REPLIES 7
tonyk86
Helper I
Helper I

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?

tonyk86_3-1644961395901.png

 

 

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

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?

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
Helper I
Helper I

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

jennratten
Super User
Super User

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors