Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
 Calculate STDEV.png
 
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

Wait, why can't you use:

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

 

?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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])))
 
PBI.pngstd dev.png
Anonymous
Not applicable

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

Anonymous
Not applicable

Thanks Darlove, I'll have to look into the ALLSELECTED a little deeper, I'm new to DAX so I am trying to piece together what i can find from differnt post to achive what I am looking to do.  The measure you listed works when I choose a specific quarter or weeks, the next piece of this puzzle I will work on is the chart showing a rolling 13 weeks but based on 2 weeks lag (i.e. if we are in week 15 today I would want to see weeks 1-13)

Anonymous
Not applicable

Before you do, try to see how to do correctly. Always, always stick to Best Practices, if you want to produce predictable, robust, and correct models/calculations.

This should get you started:

https://www.youtube.com/watch?v=_quTwyvDfG0
https://www.youtube.com/watch?v=78d6mwR8GtA
https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/
https://radacad.com/basics-of-time-intelligence-in-dax-for-power-bi-year-to-date-quarter-to-date-mon...

Best
D

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors