cancel
Showing results for
Did you mean:
Solution Sage

## 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?

1 ACCEPTED SOLUTION
Community Support

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.

Community Support

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.

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group