Forum Discussion

mweber's avatar
mweber
Frequent Visitor
7 years ago

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 100...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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:

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

 

mweber's avatar
mweber
Frequent Visitor
7 years ago

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"

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    6 years ago

    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