cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calcular cantidad de datos sobre linea de tendencia en DAX

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

1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Community Support

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.

Frequent Visitor

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