Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Trying to Calculate Standard Deviation

Hello All, 

 

I am trying to calculate standard deviation and most of what I have found on the internet is based on columns in a table and I am struggling with how to write a DAX formula for use with an analysis server where the data pulls from. 

 

I have used this link as a basis and have made some strides in calculating the SUM and the Avg.

 

In the screenshot below I have the table in which I selected a range of weeks and Repeat Dispatches in whcih I am trying to plot standard deviation for. 

 

For SUM I used the formula:

*RDR SUM = CALCULATE(SUMX(VALUES('Dim - Calendar'[Fiscal Week]),([Repeat Dispatch 7 day Numerator]/[Repeat Dispatch Denominator])), ALLSELECTED('Dim - Calendar'[Fiscal Week]))
 
For Avg I used the formula:
*RDR Avg = CALCULATE(DIVIDE([Repeat Dispatch 7 day Numerator],[Repeat Dispatch Denominator]), ALLSELECTED('Dim - Calendar'))
 
For Standard deviation I cannot quite find how to use the previous calculations to find standard deviation which should be .0266 based on the calculations I found with an online calculator.  Any help would be appreciated. 
 
Dave
 
 
Analysis Server
DAX
selection
Standard Deviation
  • Anonymous's avatar
    Anonymous
    6 years ago

    Here's the measure:

    	STDEVX.P(
		VALUES( 'Dim - Calendar'[Fiscal Week] ),
		[your measure]
	)

     

    Please learn DAX. Here's something to show you how complex the ALLSELECTED function is:

    https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/

     

    Using in code functions the functionality of which you don't fully understand is a sure way to create things nobody will be able to understand; more, you'll be creating wrong calculations that only sometimes will be returning correct answers (and you'll be happy they do whereas they really don't). Please stay away from this practice. Learn the tool you're trying to use WELL before you start using it.

     

    Best

    D

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Those standard deviation functions would be ideal, but when I try to do something like STDEVX.S('Calendar', Repeat Dispatch 7 Day Lag %) it returns a value of .058, or 5.8% (screenshot below) which that is too large of a swing in deviation given the number set, and comparing that to the online calculator (screenshot below) I used by plugging my numbers in this number should be .0266. For that reason I feel that the formula needs something to call out to only look at the selection from the filters I applied to only look at the weeks I chose. 

       

      I have been attempting to use the STDEV functions along with trying to marry it up with a formula that would basically take the numerator and denominator of the weeks shown than add them up then allow the STDEV function do work, but I have not found out a formula that would work yet.   
       
      Here is an example of the path I am on without any success yet:  STDEVX.S(CALCULATE(SUM(VALUES('Dim - Calendar'[Fiscal Week]),([Repeat Dispatch 7 day Numerator]/[Repeat Dispatch Denominator])), ALLSELECTED('Dim - Calendar'[Fiscal Week])))
       
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Here's the measure:

        	STDEVX.P(
		VALUES( 'Dim - Calendar'[Fiscal Week] ),
		[your measure]
	)

         

        Please learn DAX. Here's something to show you how complex the ALLSELECTED function is:

        https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/

         

        Using in code functions the functionality of which you don't fully understand is a sure way to create things nobody will be able to understand; more, you'll be creating wrong calculations that only sometimes will be returning correct answers (and you'll be happy they do whereas they really don't). Please stay away from this practice. Learn the tool you're trying to use WELL before you start using it.

         

        Best

        D