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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors