Median and Standard Deviation over a data set

[Anonymous]

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

[Mariusz]

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.