Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 Ending | Site | SKU Nbr | Units |
1/6/2019 | Sycamore | 1002351566 | 4680 |
1/6/2019 | Sycamore | 1002765363 | 34260 |
1/6/2019 | Sycamore | 1002596200 | 128 |
1/6/2019 | Sycamore | 1003232393 | 700 |
1/6/2019 | Sycamore | 207585 | 29700 |
1/6/2019 | Sycamore | 1001075212 | 3392 |
1/6/2019 | Sycamore | 1002765466 | 1408 |
1/6/2019 | Sycamore | 1002765469 | 1728 |
1/6/2019 | Sycamore | 1001869486 | 1595 |
1/6/2019 | Sycamore | 1003229847 | 580 |
1/6/2019 | Sycamore | 1003229848 | 144 |
1/6/2019 | Sycamore | 1001655065 | 3072 |
1/6/2019 | Sycamore | 1002765353 | 4048 |
1/6/2019 | Sycamore | 1002765358 | 3300 |
1/6/2019 | Sycamore | 1001240215 | 3328 |
1/6/2019 | Sycamore | 1003115503 | 576 |
1/6/2019 | Kingman | 1001869486 | 935 |
1/6/2019 | Kingman | 207585 | 10116 |
1/6/2019 | Kingman | 1001240215 | 2756 |
1/6/2019 | Kingman | 1001075212 | 1056 |
1/13/2019 | Sycamore | 207585 | 56736 |
1/13/2019 | Sycamore | 1001075212 | 26160 |
1/13/2019 | Sycamore | 1002351566 | 8460 |
1/13/2019 | Sycamore | 1002765358 | 13464 |
1/13/2019 | Sycamore | 1002765469 | 17832 |
1/13/2019 | Sycamore | 1001869486 | 18920 |
1/13/2019 | Sycamore | 1002765363 | 55050 |
1/13/2019 | Sycamore | 1001240215 | 28392 |
1/13/2019 | Sycamore | 1003232393 | 904 |
1/13/2019 | Sycamore | 1003229847 | 488 |
1/13/2019 | Sycamore | 1002765466 | 656 |
1/13/2019 | Sycamore | 1001655065 | 2496 |
1/13/2019 | Sycamore | 1002765353 | 880 |
1/13/2019 | Sycamore | 1003115503 | 1392 |
1/13/2019 | Sycamore | 1003229848 | 180 |
1/13/2019 | Sycamore | 1002596200 | 128 |
1/13/2019 | Kingman | 1001240215 | 15184 |
1/13/2019 | Kingman | 1001075212 | 12880 |
1/13/2019 | Kingman | 1001869486 | 3960 |
1/13/2019 | Kingman | 207585 | 4356 |
1/20/2019 | Sycamore | 1002351566 | 10820 |
1/20/2019 | Sycamore | 1001075212 | 27680 |
1/20/2019 | Sycamore | 1002765469 | 13736 |
1/20/2019 | Sycamore | 207585 | 55548 |
1/20/2019 | Sycamore | 1002765358 | 13728 |
1/20/2019 | Sycamore | 1003115503 | 2592 |
1/20/2019 | Sycamore | 1003232393 | 228 |
1/20/2019 | Sycamore | 1002765353 | 2552 |
1/20/2019 | Sycamore | 1003229847 | 76 |
1/20/2019 | Sycamore | 1001869486 | 14410 |
1/20/2019 | Sycamore | 1002765363 | 25800 |
1/20/2019 | Sycamore | 1003229848 | 18 |
1/20/2019 | Sycamore | 1002765466 | 1200 |
1/20/2019 | Sycamore | 1001240215 | 25896 |
1/20/2019 | Sycamore | 1001655065 | 4064 |
1/20/2019 | Kingman | 1001075212 | 20928 |
1/20/2019 | Kingman | 1001869486 | 9625 |
1/20/2019 | Kingman | 1001240215 | 22620 |
1/20/2019 | Kingman | 207585 | 49896 |
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
It can be a little tricky. Here's a couple threads that I solved with some-what the same issue. Take a look and see if they help. If not, fire away some questions here
https://community.powerbi.com/t5/Desktop/Incorrect-Standard-Deviation-of-a-measure/m-p/736707
https://community.powerbi.com/t5/Desktop/Standard-deviation-on-average-weighted-price/m-p/699527
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:
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.
Thanks for the heads up regarding DIVIDE! Here's a link to a sample dataset.
Let me know if you have problems accessing it.
Thanks!
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.
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:
Why I did that was to make the data model more dax (and developer) friendly. So you end up with this data model:
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
If we focus on "20785" with dates between 12/16/2018 and 1/13/2019, here's what it is doing
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
Awesome! I'm looking to have a visual that looks something like this:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |