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 wi...
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

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Wait, why can't you use:

STDEV.P
STDEV.S
STDEVX.P
STDEVX.S

 

?

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