Forum Discussion

TonyBI's avatar
TonyBI
Helper I
7 years ago
Solved

STEYX Calculation

Estimates, I´m becoming crazy with this simple calculation in POWER BI. The idea is to calculate the tipical error of a series of values X and Y. This is very simple in Excel with the formula STEYX b...
Need Help
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

    Hi TonyBI

     

    you can build this model

     

     

    Then try this measure:

     

    STEXY =
CALCULATE (
    IF (
        COUNTROWS ( Data ) < 3,
        "Data must have at least 3 records",
        VAR Y_AVG =
            AVERAGE ( Data[Y] )
        VAR X_AVG =
            AVERAGE ( Data[X] )
        VAR Y_DEV =
            SUMX ( Data, POWER ( Data[Y] - Y_AVG, 2 ) )
        VAR X_DEV =
            SUMX ( Data, POWER ( Data[X] - X_AVG, 2 ) )
        VAR XY_DEV =
            POWER ( SUMX ( Data, ( Data[Y] - Y_AVG ) * ( Data[X] - X_AVG ) ), 2 )
        VAR ERR_ =
            SQRT (
                ( Y_DEV - DIVIDE ( XY_DEV, X_DEV ) )
                    * DIVIDE ( 1, COUNTROWS ( Data ) - 2 )
            )
        RETURN
            ERR_
    ),
    NOT ( ISBLANK ( Data[X] ) ),
    NOT ( ISBLANK ( Data[Y] ) )
)
TonyBI's avatar
TonyBI
Helper I
7 years ago

Just another thing... this is just an example... the data set is huge, so thats why I need to be able to filter (names are related by other static atributes, like Type of Name, Region).. thx!!

  • LivioLanzo's avatar
    LivioLanzo
    Solution Sage
    7 years ago

    Hi TonyBI

     

    you can build this model

     

     

    Then try this measure:

     

    STEXY =
CALCULATE (
    IF (
        COUNTROWS ( Data ) < 3,
        "Data must have at least 3 records",
        VAR Y_AVG =
            AVERAGE ( Data[Y] )
        VAR X_AVG =
            AVERAGE ( Data[X] )
        VAR Y_DEV =
            SUMX ( Data, POWER ( Data[Y] - Y_AVG, 2 ) )
        VAR X_DEV =
            SUMX ( Data, POWER ( Data[X] - X_AVG, 2 ) )
        VAR XY_DEV =
            POWER ( SUMX ( Data, ( Data[Y] - Y_AVG ) * ( Data[X] - X_AVG ) ), 2 )
        VAR ERR_ =
            SQRT (
                ( Y_DEV - DIVIDE ( XY_DEV, X_DEV ) )
                    * DIVIDE ( 1, COUNTROWS ( Data ) - 2 )
            )
        RETURN
            ERR_
    ),
    NOT ( ISBLANK ( Data[X] ) ),
    NOT ( ISBLANK ( Data[Y] ) )
)
    • TonyBI's avatar
      TonyBI
      Helper I
      7 years ago

      This is working perfectly! I´ll try it with the "Huge" data set, but I think It should have the same results. Thx you very much LivioLanzo!!