cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## Need help to create a certain meassure.

Hallo everyone,

i would like to write a meassure that works quite similar to the following:

Test = Calculate(STDEV.P(Table1[Value]),ALLEXCEPT(Table1,Table1[Column1]))

My problem ist that i need to make a little change in the formula of the Standard Deviation. I don't want to use the average in the calculation. I want to use the median instead. Can you help me write a meassure that can do the calculation?

Best wishes,

Tobias

1 ACCEPTED SOLUTION
Super User

Is this a measure or a calculated column? I do not quite understand the role of the ALLEXCEPT in your code, I would need more details. Based on that code  you could try something like this for the operation itself, and then adapt it with the ALLEXCEPT as needed

``````VAR median_ = MEDIAN ( Table1[Value] )
VAR N_ = COUNT ( Table1[Value] )
RETURN
SQRT (
DIVIDE ( SUMX ( Table1, POWER ( Table1[Value] - median_, 2 ) ), N_ - 1 )
)``````

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

3 REPLIES 3
Super User

Hi @TobiasV

Can you show  what the exact formula would be?

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

New Member

The formula is

sqrt(  sum( x-median(x) )^2 / (n-1) )

with x are the entries of Table1[Value] and n is the number of entries of Table1[Value].

Super User

Is this a measure or a calculated column? I do not quite understand the role of the ALLEXCEPT in your code, I would need more details. Based on that code  you could try something like this for the operation itself, and then adapt it with the ALLEXCEPT as needed

``````VAR median_ = MEDIAN ( Table1[Value] )
VAR N_ = COUNT ( Table1[Value] )
RETURN
SQRT (
DIVIDE ( SUMX ( Table1, POWER ( Table1[Value] - median_, 2 ) ), N_ - 1 )
)``````

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors