Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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.
Hola, como se puede calcular la distancia entre los puntos y la línea de tendencia, existe alguna formula en dax?