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
(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
window_avg(sum([Y 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])
if sum([Y Axis]) > [Linear regression] then 'Above'
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.
Linear Regression Help.twbx.zip 947.9 KB
This is great. I have 2 questions for you.
- Lets say I want to force the Y-intercept to 0. Then what would be the tweaked formula to calculate Beta?
- Also do you know if there is a way I can display the equation on the trend line always