Before we start, be sure to use the correct data source located here.
It’s important to know this custom dataset is designed for challenges in building a marketing or sales pipeline. There is only one row per “opportunity”. And an opportunity will cross many stages. Most organizations use each stage to allocate appropriate resources in order to close a deal.
The challenge in working with this data is we often want to have the data accounted in multiple stages–not just the current stage, but all previous stages, too. This is the challenge provided.
Step #1: Set the framework
Add [Last Stage] to rows and [value] to columns. Make sure bar chart is selected.
Step #2: Create a sort calculation and sort rows
We’ll want to sort our stages. So create the following calculation called [sort].
//sort CASE [Last Stage] WHEN "Prospect" THEN 1 WHEN "Lead" THEN 2 WHEN "Qualified" THEN 3 WHEN "Opportunity" THEN 4 WHEN "Negotiations" THEN 5 WHEN "Closed" THEN 6 END
Now sort [Last Stage] ascending by the sort calculation:
Step #3: Create the sales funnel calculation
Add SUM([Values]) to columns, again. Then make it a RUNNING_SUM(). For the second calculation you need to create a running sum of the [value].
The only problem is that the running sum shouldn’t be in the down direction, rather it should be running upward. This is not an out-of-the-box functionality with Tableau, but there is a way it can be done by customizing the Table Calculation.
Edit the table calculation and select Specific Dimensions. Make sure [Last Stage] is selected. Below, on Sort Order, create a custom sort using the [sort] field, select minimum aggregation and choose the sort order as descending.
Click-, hold-, and-drag this table calculation over to measures on the data pane. Name the calculation [sales funnel].
Step #4: Create the percent of stage calculation
For the last calculation, we are going to calculate what percent of closed sales went through each stage using another Table Calculation. Write the following calculation:
// percent of stage LOOKUP(SUM([value]), LAST()) / [sales funnel]
Add this calculation to columns. Then to the right of all three calculations on columns: double-click and type MIN(1.0). Make a dual axis between MIN(1.0) and [percent of stage]. Synchronize the axes. Make sure all mark types are bars. Change the opacity of the bar color to 35%.
Step #5: Format
Format the labels to the correct data type. Hide the headers. For color, use #16666D.
Need more help making your Tableau dashboards a work of art? Our knowledgeable team of Tableau experts is here to help!