Performing Simple Linear Regression in Tableau

Today, we will talk about performing simple linear regression in Tableau.  You can see the effects of this by simply turning on trend lines.  However, we don't know of a built-in way to get the equation of that line out of Tableau.  You might ask "Why would I want to know the equation of the line?"  Imagine that you wanted to find out whether a particular point was above or below the line, i.e. above or below average.  If you had the equation of the line, it's nothing more than a simple algebraic exercise (which we won't discuss here).  As usual, we will use the Superstore Sales sample data set in Tableau.

Step 1:

• Create a scatterplot of Profit vs. Sales per Product
• Add a Trend Line to the chart

Profit vs. Sales per Product

Step 2:

• Find the means for Sales and Profit
• Create the following calculated fields

X Mean (Sales)

Y Mean (Profit)

I labelled these X and Y instead of Sales and Profit because it makes it easier to remember where they go in the formulas.  You can name these anything you wish, as long as you know what they are.  Since this procedure is involved, it's always best to drag the values onto the Label Shelf to see if they are calculated properly.

Means (Test)

The means are the same for every point, and they seem to be reasonable values.  This is good enough for us.
Step 3:

• Calculate the difference of each point from the mean for Sales and Profit, individually
• Create the following calculated fields

X Differences

Y Differences

Now, let's check our work.

Differences (Test)

The values are different for each point, and get larger as they get further from the means.  Check #2 complete.

Step 4:

• Calculate the Sum of Squares for XX and XY
• Create the following calculated field

SSxx

SSxy

We won't get into why these formulas are called Sum of Squares, or why they look the way they do.  Just know that these are the mathematically correct formulas.  Now, let's check our work.

Sum of Squares (Test)

We can see that the values are the same for every point, and are really big (which is about all we can decipher from such a value).  Check #3 complete.

Step 5:

• Calculate the Slope and Y Intercept
• Create the following calculated fields

Slope

Y Intercept

Finally, let's check to see if these values are the same as what Tableau calculates.

Slope and Intercept (Test)

Drawing back on high school, you might remember that Slope-Intercept form is y = mx + b.  We can see that our values match the values for the trend line.  Now, we can use these values for any other calculations.  Thanks for reading.  We hope you found this informative.

P.S.

For those of you that remember the Z-Test package we published to the Tableau forums a while ago, it has been updated to include this procedure as well.  We have also created another document describing this technique at http://community.tableausoftware.com/docs/DOC-1479.

Brad Llewellyn

Associate Consultant

Mariner, LLC

llewellyn.wb@gmail.com
https://www.linkedin.com/in/bradllewellyn