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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mweber
Frequent Visitor

Standard Deviation

Hello,

 

I'm having trouble getting the correct Standard Deviation from a dataset. Here's a subsection of a datset I'm working with:

Week EndingSiteSKU NbrUnits
1/6/2019Sycamore10023515664680
1/6/2019Sycamore100276536334260
1/6/2019Sycamore1002596200128
1/6/2019Sycamore1003232393700
1/6/2019Sycamore20758529700
1/6/2019Sycamore10010752123392
1/6/2019Sycamore10027654661408
1/6/2019Sycamore10027654691728
1/6/2019Sycamore10018694861595
1/6/2019Sycamore1003229847580
1/6/2019Sycamore1003229848144
1/6/2019Sycamore10016550653072
1/6/2019Sycamore10027653534048
1/6/2019Sycamore10027653583300
1/6/2019Sycamore10012402153328
1/6/2019Sycamore1003115503576
1/6/2019Kingman1001869486935
1/6/2019Kingman20758510116
1/6/2019Kingman10012402152756
1/6/2019Kingman10010752121056
1/13/2019Sycamore20758556736
1/13/2019Sycamore100107521226160
1/13/2019Sycamore10023515668460
1/13/2019Sycamore100276535813464
1/13/2019Sycamore100276546917832
1/13/2019Sycamore100186948618920
1/13/2019Sycamore100276536355050
1/13/2019Sycamore100124021528392
1/13/2019Sycamore1003232393904
1/13/2019Sycamore1003229847488
1/13/2019Sycamore1002765466656
1/13/2019Sycamore10016550652496
1/13/2019Sycamore1002765353880
1/13/2019Sycamore10031155031392
1/13/2019Sycamore1003229848180
1/13/2019Sycamore1002596200128
1/13/2019Kingman100124021515184
1/13/2019Kingman100107521212880
1/13/2019Kingman10018694863960
1/13/2019Kingman2075854356
1/20/2019Sycamore100235156610820
1/20/2019Sycamore100107521227680
1/20/2019Sycamore100276546913736
1/20/2019Sycamore20758555548
1/20/2019Sycamore100276535813728
1/20/2019Sycamore10031155032592
1/20/2019Sycamore1003232393228
1/20/2019Sycamore10027653532552
1/20/2019Sycamore100322984776
1/20/2019Sycamore100186948614410
1/20/2019Sycamore100276536325800
1/20/2019Sycamore100322984818
1/20/2019Sycamore10027654661200
1/20/2019Sycamore100124021525896
1/20/2019Sycamore10016550654064
1/20/2019Kingman100107521220928
1/20/2019Kingman10018694869625
1/20/2019Kingman100124021522620
1/20/2019Kingman20758549896

 

What I'm trying to accomplish is to simply get the Standard Deviation of Average Weekly Units for each SKU Nbr. I've been succesful at creating a measure that will get me the weekly avg. but can't seem to get the Standard Dev.  In excel, all you have to do is create a pivot on week ending, sum up the SKU, and see what the average and StD is across all weeks. (e.g. the answer for 207585 is avg. per week=68,784 & Standard Deviation=27,339) I can't seem to replicate this using DAX. Any help would be greatly appreciated. 

 

MWeber

10 REPLIES 10
Anonymous
Not applicable

Thanks Nick_M! I've looked through your threads and tried a couple things but not sure I'm applying them correctly. So I created this measure to calculate Avg. Weekly Sales:

 

Avg. Units per Week = SUM('Summarized Orders'[Total])/CALCULATE(DISTINCTCOUNT('Summarized Orders'[Calendar Week End]))
 
I was thinking maybe then there was a way to leverage that measure. Tried the addcolumns with no success.
 
Thanks again for the assistance!
 
 

 

 

Anonymous
Not applicable

Can you upload some sample data/pbix file?  One drive works well.

 

Also, always best to use the DIVIDE function and not "/", since th DIVIDE function has built-in error catching. Not the end of the world, but something I found to more helpful than not. 

 

https://docs.microsoft.com/en-us/dax/divide-function-dax

Thanks for the heads up regarding DIVIDE! Here's a link to a sample dataset. 

 

https://amplastics-my.sharepoint.com/:x:/p/mweber/Ed7Qt5YQtWxDs1vonH2W1sQBpbMNtrAFXxvr-ZvM9vrTbA?e=Y...

 

Let me know if you have problems accessing it.

 

Thanks!

Anonymous
Not applicable

I was able to get it no problem. Can you do a quick mock up of what you would like the result to be? Having a little trouble seeing the required output.

Nick_M,

 

Any way you can share the solution? Apprecaite it!


@Anonymous wrote:

I was able to get it no problem. Can you do a quick mock up of what you would like the result to be? Having a little trouble seeing the required output.


 

Anonymous
Not applicable

@mweber 

I meant I was able to get your sample data no problem 🙂

 

But take a look at the following.  I dont think it's 100% what you were looking for, but it could be a great start. I just dont know your data and what exactly you were looking for.

 

Here's what I did:

  • Created a date table, with a "WeekEnding"Column
  • Created a DimSite table.  Both of those were done in Power Query, so you can step through on how I did that.  

Why I did that was to make the data model more dax (and developer) friendly.  So you end up with this data model:

Data Model.png

I put End of Week as a slicer and SKU Nbr( which really should be another Dimension table) and Site from Dimsite on rows.  Then wrote these measures:

Total Units = SUM ( FactTable[Total] )

Avg Units = AVERAGE( [Total])

Standard Deviation = 
STDEVX.P( FactTable,[Total Units])

Which gives this matrix

Final Matrix.png

 

If we focus on "20785" with dates between 12/16/2018 and 1/13/2019, here's what it is doing

Excel Breakdown.png

 

But then you can slice by any sort of date range, site, SKU, etc...  Seemed pretty straightforward, which makes me wonder if this is what you were after, especially since your Total column in your fact table was at the week end level already. It would be a little more complex, not much though, if it was at a granularity lower and had to sum up to get that total and then the average and then the standard deviation. But maybe you want the std dev of the average?  

 

Let me know your thoughts

 

-Nick

 

Thanks Nick! I was actually looking for the Standard Deviation of the weekly average for each SKU. I created the measure: 

 

Avg. Units per Week = SUM('Summarized Orders'[Total])/CALCULATE(DISTINCTCOUNT('Summarized Orders'[Calendar Week End]))

 

This gets me the correct avg. per week for each SKU when put into a visiual. Now I need to know what is the standard deviation of the weekly avg. "On average we sell X amount of 207585 per week with a standard deviation of Y"

Hi @mweber ,

I'm a little confused about your requirement.

It seems that the solution of Nick_M should be helpful.

What is your desired output based on your data sample?

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Awesome! I'm looking to have a visual that looks something like this:

 

Capture.PNG

 

Avg Units/Week is the previous measure I created and was hoping to have this new StD next to it. This way I can create a slicer to toggle the timeframe... hoping to answer questions around averages & standard deviations for certain periods.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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