Calcular cantidad de datos sobre linea de tendencia en DAX

[J3]

Hola, por favor necesito saber si hay alguna forma de calcular la cantidad de datos en un grafico de dispersión que están por sobre la linea de tendencia 

[Anonymous · Accepted solution]

Hi @J3 ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a measure as a trend value.

Estimated Sales = 
VAR Known =
    FILTER (
        SELECTCOLUMNS (
            ALLSELECTED ( 'Date'[Date] ),
            "Known[X]", 'Date'[Date],
            "Known[Y]", [Actual Sales]
        ),
        AND (
            NOT ( ISBLANK ( Known[X] ) ),
            NOT ( ISBLANK ( Known[Y] ) )
        )
    )
VAR SlopeIntercept =
    LINESTX(Known, Known[Y], Known[X])
VAR Slope =
    SELECTCOLUMNS(SlopeIntercept, [Slope1])
VAR Intercept = 
    SELECTCOLUMNS(SlopeIntercept, [Intercept])
RETURN
    SUMX (
        DISTINCT ( 'Date'[Date] ),
        Intercept + Slope * 'Date'[Date]
    )

(3)Create another measure as the actual value.

Actual Sales = SUM(Sales[Amount])

(4)Compare trend metrics with actual metrics and calculate quantities.

Above = IF([Actual Sales]>[Estimated Sales],1,0)

Count Above = SUMX(VALUES('Date'[Date]),[Above])

You can also refer to the following posts to create trendline measure.

Linear regression in DAX – XXL BI

Use Custom DAX to create a Linear Regression Trendline with a Dynamic Date Hierarchy in Power BI

DAX to create a Trend line? - Page 2 - Microsoft Fabric Community

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[J3]

Hola, como se puede calcular la distancia entre los puntos y la línea de tendencia, existe alguna formula en dax?