Forum Discussion

jthomson's avatar
jthomson
Solution Sage
5 years ago
Solved

Trend line values

Hi,

 

Simple enough question - I have some data, two columns, cost of sale and date of sale. I've plotted average cost of sale against time in a line graph. I've added a trend line from the analytics pane to show the overall changes over time - while I can look at roughly what the value is by comparing against the y-axis, I can't seem to find a way to get what the actual value is at any point in time. Is there a way to get this displayed, say as a tooltip, or failing that, does anyone know how Power BI calculates this trend line so I can try to replicate it as a DAX measure?

Need Help
Tips and Tricks
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi jthomson ,

    The trend line calulation formula by DAX is like this:

    Linear regression = 
VAR Known =
    FILTER (
        SELECTCOLUMNS (
            ALLSELECTED ( 'Table'[Date] ),
            "Known[X]", 'Table'[Date],
            "Known[Y]", CALCULATE ( SUM ( 'Table'[Sales] ) )
        ),
        AND ( NOT ( ISBLANK ( Known[X] ) ), NOT ( ISBLANK ( Known[Y] ) ) )
    )
VAR Count_Items =
    COUNTROWS ( Known )
VAR Sum_X =
    SUMX ( Known, Known[X] )
VAR Sum_X2 =
    SUMX ( Known, Known[X] * Known[X] )
VAR Sum_Y =
    SUMX ( Known, Known[Y] )
VAR Sum_XY =
    SUMX ( Known, Known[X] * Known[Y] )
VAR Average_X =
    AVERAGEX ( Known, Known[X] )
VAR Average_Y =
    AVERAGEX ( Known, Known[Y] )
VAR Slope =
    DIVIDE (
        Count_Items * Sum_XY - Sum_X * Sum_Y,
        Count_Items * Sum_X2 - Sum_X * Sum_X
    )
VAR Intercept = Average_Y - Slope * Average_X
RETURN
    SUMX ( DISTINCT ( 'Table'[Date] ), Intercept + Slope * 'Table'[Date] )

    It will be the same as the trend line in the Analysis pane:

    Attached a sample file in the below, hopes to help you.

     

    In addtion, this blog introduces the calculation logic in details that you can refer: Simple linear regression in DAX 

    Similiar issue: DAX to create a Trend line? 

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    Hi jthomson ,

    The trend line calulation formula by DAX is like this:

    Linear regression = 
VAR Known =
    FILTER (
        SELECTCOLUMNS (
            ALLSELECTED ( 'Table'[Date] ),
            "Known[X]", 'Table'[Date],
            "Known[Y]", CALCULATE ( SUM ( 'Table'[Sales] ) )
        ),
        AND ( NOT ( ISBLANK ( Known[X] ) ), NOT ( ISBLANK ( Known[Y] ) ) )
    )
VAR Count_Items =
    COUNTROWS ( Known )
VAR Sum_X =
    SUMX ( Known, Known[X] )
VAR Sum_X2 =
    SUMX ( Known, Known[X] * Known[X] )
VAR Sum_Y =
    SUMX ( Known, Known[Y] )
VAR Sum_XY =
    SUMX ( Known, Known[X] * Known[Y] )
VAR Average_X =
    AVERAGEX ( Known, Known[X] )
VAR Average_Y =
    AVERAGEX ( Known, Known[Y] )
VAR Slope =
    DIVIDE (
        Count_Items * Sum_XY - Sum_X * Sum_Y,
        Count_Items * Sum_X2 - Sum_X * Sum_X
    )
VAR Intercept = Average_Y - Slope * Average_X
RETURN
    SUMX ( DISTINCT ( 'Table'[Date] ), Intercept + Slope * 'Table'[Date] )

    It will be the same as the trend line in the Analysis pane:

    Attached a sample file in the below, hopes to help you.

     

    In addtion, this blog introduces the calculation logic in details that you can refer: Simple linear regression in DAX 

    Similiar issue: DAX to create a Trend line? 

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • kalich22's avatar
      kalich22
      Regular Visitor
      3 years ago

      Thanks for this, looks scary but very fast to implement.