3 Replies Latest reply on Mar 26, 2015 11:18 PM by Bora Beran

# Regression Line Calculation

Okay, so I'm using the built-in Tableau Linear regression to evaluate Shipping Cost (Y) vs. Sales (X) marks on a scatter plot.  I want to create a calculation that evaluates whether each mark is above or below the regression line.  I then want to use this calculation for shapes.  I only need two shapes, but I'm having trouble with the proper calculation.

In the attached Workbook, the Performance calculated field is my attempt which didn't work.

• ###### 1. Re: Regression Line Calculation

Hi Dwight,

You need to replicate the linear regression formula that tableau uses to plot the trend line. The equation to use is as on wiki: https://en.wikipedia.org/wiki/Simple_linear_regression

In Tableau, that translates to creating a calculated field for beta as

Beta =

(WINDOW_AVG(sum([X Axis])*sum([Y Axis]))-(WINDOW_AVG(sum([X Axis]))*WINDOW_AVG(sum([Y Axis]))))

/ (WINDOW_AVG(sum([X Axis])^2)-(WINDOW_AVG(sum([X Axis]))^2))

and alpha as

Alpha =

window_avg(sum([Y Axis])) -

[Beta]*window_avg(sum([X Axis]))

You can then combine them to create a linear regression line, which in turn can be used in another calculated field to determine whether the points on the chart are above or below this line

Linear Regression = [Alpha] + [Beta] * sum([X Axis])

Performance =

if sum([Y Axis]) > [Linear regression] then 'Above'

else 'Below'

end

You need to pay special attention to how the table calculations are partitioned and addressed

Hope that helps (and special thanks to Joe Mako on this forum post here: http://community.tableau.com/thread/121346)

See the attached workbook for a working version.

• ###### 2. Re: Regression Line Calculation

Hi Robin

This is great. I have 2 questions for you.

1. Lets say I want to force the Y-intercept to 0. Then what would be the tweaked formula to calculate Beta?
2. Also do you know if there is a way I can display the equation on the trend line always

Regards

• ###### 3. Re: Regression Line Calculation

Here is an example. It computes both slope and pearson's correlation coefficient and shows in the tooltip. Also colors the background based on the slope (red for negative, green for positive correlation)