Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
TobiasV
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

@TobiasV 

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 

 

SU18_powerbi_badge

View solution in original post

3 REPLIES 3
AlB
Super User
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 

 

SU18_powerbi_badge

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].

 

 

@TobiasV 

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 

 

SU18_powerbi_badge

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.