STEYX Calculation

[TonyBI]

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 but I cannot obtain the same result in Power BI, here is the formula for STEYX and the data set:

 (here "n" is the number of values, in this case 10 - because Name 11 doesn´t have values of X nor Y)

 

But First things first, the calculation must be done only with the Names that has values (here Name 11 doesn´t have values, so it must not be considered in the calculation). In this example STEYX is 32.75.

When I try to do this in Power BI, I tried to separate the formula for every cumulative shown. So, the first calculation is (X-averageX)^2

The values for every row are OK, but the cumulative value is not (it contain Name 11 and doesn´t sum correctly - I should have obtained 8488.4 in the total value)

So, to summarize... I want to calculate the STEYX in Power BI (formula shown above), but the calculation must use only those names that have values X and Y, and of course this calculation should change if I select diferent Names in the "Name Filter".

Thx in advance!!

[TonyBI]

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 · Accepted solution]

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]

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!!