Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Solved! Go to Solution.
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
Wait, why can't you use:
STDEV.P
STDEV.S
STDEVX.P
STDEVX.S
?
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.
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
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)
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |