Manually Calculating Trend Lines

Published: 28 Apr 2015
Last Modified Date: 20 Jul 2023

Question

How to calculate trend lines manually so values such as slope can be accessed as a field.

Environment

Tableau Desktop

Answer

Tableau returns values like slope of trend line, covariance, R-squared and Pearson correlation coefficient when you add a trend line and then select "Describe Trend Model". For more information about trend lines please see Add Trend Lines to a Visualization.

Option 1

Right-click the trend line and select "Describe Trend Models"
Copy the trend line model equation in a calculated field.

However, please note that as data changes (i.e. data is filtered or excluded), the calculated fields would need to be updated. For more information regarding this method, please refer to the reply in this Community post.

Option 2

Use table calculations to calculate the trend line and slope. To view an example, see the attached workbook called trend_line.twbx under the Attachments section on the right side of this article.
1. Create the calculated fields as follows:

Name: slope
Value: WINDOW_COVAR(SUM([X]), SUM([Y])) / WINDOW_VAR(SUM([X]))
Name: Intercept
Value: WINDOW_AVG(SUM([Y])) - [slope] * WINDOW_AVG(SUM([X]))
Name: trend line
Value: SUM([X])*[slope]+[Intercept]

2. Drag x to the column and y to the row.
3. Drag [trend line] to the row, and right-click it to make it dual-axes.
4. Right-click [trend line] axis and select [Synchronize Axis], and uncheck [show header].
5. Change [trend line] to line if it is not a line.

Option 3
It is also possible to use the COVAR() function to create a calculated field which returns the slope of a trend line. For more information about how this can be done please see Covariance, Trend Lines, Correlation Coefficient R and R-Squared in the Community.