Skip to main content
cancel
Showing results for 
Search instead 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

Reply
J3
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 

J3_0-1692884828201.png

 

1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
Community Support

Hi @J3 ,

 

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

(1) This is my test data. 

vtangjiemsft_0-1693192158426.png

(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])

 

vtangjiemsft_1-1693192399443.png

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. 

View solution in original post

2 REPLIES 2
v-tangjie-msft
Community Support
Community Support

Hi @J3 ,

 

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

(1) This is my test data. 

vtangjiemsft_0-1693192158426.png

(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])

 

vtangjiemsft_1-1693192399443.png

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?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.