Creating Histograms in Tableau

Today, we will talk about how to create histograms in Tableau.  When we say histogram, we are referring to the statistical concept of a histogram.  Basically, this means we are looking at the distribution of values of a continuous variable.  The following is a simple example.  As usual, we will use the Superstore Sales sample data set in Tableau.

Sales by Container

This shows a continuous variable sliced by a discrete dimension.  Despite the fact that some people refer to this as a histogram, we call it a "Bar Chart."

Histogram of Sales

This is what we refer to as a histogram.  It shows the distribution of sales.  Now, we will look at how to create these in Tableau.  We will also show you a trick that we recently discovered for creating histograms at higher grain than the underlying data.

Step 1:

• Right-click [Sales] -> Select "Create Bins..."

Sales (bin)

Step 2:

• Add [Sales (bin)] to the Columns Shelf
• Add [Number of Records] to the Rows Shelf

Histogram of Sales (again)

Now, you have created a histogram and can see that smaller [Sales] are more common than expensive ones.  While this is good, what if you want to look at SUM( [Profit] ) per [Customer]?  To our knowledge, there is no built-in way to do this in Tableau.  Have no fear; there is a way!!!

Step 1:

• Create a [Bin Size] Parameter

Bin Size

Step 2:

• Calculate the bin that corresponds to SUM( [Profit] )
• Create the following calculated field

Sum Profit (bin)

Step 3:

• Calculate a 1 for each member at our new grain (which will be Customer)
• Create the following calculated field

Number of Observations

Step 4:

• Drag [Customer] to the Detail Shelf
• Drag [Sum Profit (bin)] to the Columns Shelf
• Drag [Number of Observations] to the Rows Shelf
• Click on the Colors Shelf -> Change "Border" to "None"

Histogram of Profit per Customer

You may notice something odd about this histogram.  The zero bin seems to have a REALLY large number of values in it.  This is actually because most of our bins are calculated as -200 to -100 or 100 to 200, yet the 0 bin is calculated as -100 to 100.  That's twice as many possible values!!!  Let's fix this.

Step 5:

• Shift the negative profits back one space to create room for the extra bin.
• Create the following calculated field

Sum Profit (bin) (Corrected)

Now, we can test this to see if our hypothesis was correct.

Sum Profit (bin) Test

We were right!!! There were two sets of values in the zero bin.  Now, let's remove [Sum Profit (bin)] from the chart to get our final histogram.

Histogram of Profit per Customer (Corrected)

Now, we can see that most customers are worth between -$500 and $1000.  While creating this method, we did find one interesting flaw in this design.  We could not find a way to calculate the total number of observations in each bin.  We tried using WINDOW_SUM(), but were unable to get it to restart because [Sum Profit (bin)] is not a dimension, but a discrete measure.  If anyone knows any way to get around this, let us know.  We also have another treat.  Remember the Z-Test workbook we created a couple of weeks ago?  We were able to use this technique to add a histogram sheet to that workbook.  Here's a sneak peek.

Z-Test Histograms

The updated Z-Test workbook can be found at http://community.tableausoftware.com/docs/DOC-1429.  We hope you found this informative.  Thanks for reading.

Brad Llewellyn

Associate Consultant

Mariner, LLC

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

What-If Analysis in Tableau

Today, we will talk about What-If Analysis in Tableau.  What-If Analysis is when you propose a hypothetical scenario and use it to see how it will affect your business.  A simple example is, how much will my profit increase if I stop doing business with unprofitable customers?  As usual, we will use the Superstore Sales sample data set in Tableau.

Step 1:

• Find the unprofitable customers

Unprofitable Customers

Step 2:

• Create a line graph of Profit per Year, segregated by Profitable/Unprofitable Customers
• Use the Pages Shelf to hide the Unprofitable line.

Profit by Year, from Profitable Customers

Step 3:

• Calculate the Total Profit using both Profitable and Unprofitable Customers
• Create the following table calculation

Total Profit

Step 4:

• Use this table calculation to create a synchronized, dual-axis line chart.

Total Profit and Profit from Profitable Customers by Year

As you can see, there is a pretty significant increase in Profit if we stop doing business with unprofitable customers.  Some of you may say, "This isn't the kind of What-If Analysis I'm used to!!!"  You're right.  This example was useful for showing the strength of Tableau in the area.  

Now, let's look a more traditional What-If Analysis.  What would happen to my profit if I decreased my shipping cost by X%?

Step 1:

• Create a parameter for the X%

Shipping Cost Decrease Percentage

Step 2:

• Calculate the new profit
• Create the following calculated field

New Profit

Some of you might be asking, "Why did you calculate profit that way?"  The simple answer is, I didn't have the right variables in my data set to calculate Profit the traditional way, i.e. Profit = Sales - Costs.  At its simplest, this way adds the Shipping Cost back into the Profit.  There are some much more complicated ways to do something like this, but for the sake of simplicity, we'll keep it like this for now.

Step 3:

• Create a line chart to compare Profit and New Profit by Year

Profit and New Profit by Year

As you can see, if you can decrease shipping costs by 20%, you can increase profit by about 1% or 2%.  While these aren't substantial numbers, there's a very important concept to remember.  Shipping is a notoriously inefficient business.  It's not uncommon for companies to reduce shipping costs 20% or 30% just by using a new piece of technology or creating a new initiative.  Also, it's pretty easy to make your boss smile if you can show him a way to increase his profits by a few percent each year.  I hope you found this informative.  Thanks for reading.

Brad Llewellyn

Associate Consultant

Mariner, LLC

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