cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## Median and Standard Deviation over a data set

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

Community Champion

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors