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

    Regression Line Calculation

    Dwight Taylor

      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.

       

      Please help.  The workbook is attached.

        • 1. Re: Regression Line Calculation
          Robin Kennedy

          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
            Karthik Ravikumar

            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
              Bora Beran

              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)

              http://public.tableausoftware.com/static/images/Tr/TrellisWithLinearRegression/TrellisChartwithLinearRegression/1.png