Forum Discussion
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 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
10 Replies
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:
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!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.
