Median and Standard Deviation over a data set

Anonymous — Thu, 31 Oct 2019 10:15:37 GMT

Hello

I’ve got the data set and a number of measures ...

The first measure [Success_Count] calculates the number of Successes and the second [Average_Success] calculates the average Monthly success…

Success_Count = CALCULATE (
DISTINCTCOUNT(ExamResults[StudentID]),
FILTER (
ExamResults,
ExamResults[Months_on_Course] >= 3
&& ( ( ExamResults[ExamStatus] = "Pass" )
|| ( ExamResults[ExamStatus] = "Distinction" ) )
)
)

Average_Success = DIVIDE([# Success_Count] ,DISTINCTCOUNT ( ExamResults[StartDate+3M]) )

My question is, how do I calculate the Median and Standard Deviation values by month across the data set. I've tried it various different ways using Calculated table
GroupBy etc,but can't seem to get it to work correctly, or get the "Student_Sum" below value into a Calculate function?

DEFINE
VAR myGroup =
FILTER (
GROUPBY (
ExamResults,
ExamResults[Months_on_Course],
ExamResults[ExamStatus],
ExamResults[StartDate+3M],
"Student_Count", COUNTX ( CURRENTGROUP (), ExamResults[StudentID] )
),
ExamResults[Months_on_Course] >= 3
&& ( ( ExamResults[ExamStatus] = "Pass" )
|| ( ExamResults[ExamStatus] = "Distinction" ) )
)
EVALUATE
GROUPBY (
myGroup,
ExamResults[StartDate+3M],
"Student_Sum", SUMX ( CURRENTGROUP (), [Student_Count] )
)

Any suggestion much appreciated

Re: Median and Standard Deviation over a data set

Mariusz — Thu, 31 Oct 2019 11:44:19 GMT

Hi Anonymous

Try the below DAX function.

Standard Deviation Sales Qty = STDEV.P( Sales[Quantity] )

Median Sales Qty = MEDIAN( Sales[Quantity] )

There are X functions available in both cases, so you can perform aggregations on different granularities if needed.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

topic Re: Median and Standard Deviation over a data set in DAX Commands and Tips

Median and Standard Deviation over a data set

Re: Median and Standard Deviation over a data set