Forum Discussion

TobiasV's avatar
TobiasV
New Member
6 years ago
Solved

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

Need Help
  • AlB's avatar
    AlB
    6 years ago

    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 

     

3 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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 

     

    • TobiasV's avatar
      TobiasV
      New Member
      6 years ago

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

       

       

      • AlB's avatar
        AlB
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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