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

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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.

    • mweber's avatar
      mweber
      Frequent Visitor
      7 years ago

      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.

    • mweber's avatar
      mweber
      Frequent Visitor
      7 years ago

      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'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