Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Trend line values

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Trend line values

02-03-2021
02:25 AM

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?

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-04-2021
05:56 PM

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.

1 REPLY 1

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-04-2021
05:56 PM

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

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

Featured Topics

Top Solution Authors

User | Count |
---|---|

75612 | |

58463 | |

49266 | |

33480 | |

19284 |

Top Kudoed Authors

User | Count |
---|---|

465 | |

292 | |

74 | |

74 | |

53 |