Do you want to know the top 30% of a population as well as the bottom 30%? How do you get the distinct count of customers in these groups? There are a couple of different methods I found to obtain this split in Tableau.
You can download the workbook behind this blog from my Tableau Public profile here.
1st Method - Table Calculation
In this first method I used a Level of Detail Expression and Rank calculations to figure out the 30/40/30 split.
Calculations
Sales Rank
Rank (SUM([Sales]),'Desc')
LOD Total Cust Count
{ Fixed : COUNTD([Customer Name]) }
Sales Rank %
([Rank Sales]/SUM([LOD Total Cust Count]))*100
Top Middle Bottom Label
IF [Rank Sales %] < 30 then "Top"
ELSEIF [Rank Sales %] > 70 then "Bottom"
Else "Middle"
END
How to Set Up the Worksheet
Place Customer Name on the Rows shelf.
Place Sales on the Text Marks card.
Sort the Customer Name by Sales Descending.
Add Sales Rank to the Rows shelf. (Make sure it is formatted as Discrete and the Table Calculation is Table down.)
Add the LOD Total Cust Count to the Rows shelf. (Make sure it is Discrete.)
Add Sales Rank % to the Rows shelf. (Make sure it is Discrete.)
Place Top Middle Bottom Label on the Color Marks card.
*If you want a label for the Sales value, you can right-click on the Columns shelf to create a New Calculation. Simply type in "Sales". You will have to hide the field label for this column.
Your worksheet should appear as below;
Issues with this method
Because this is a table calculation, it will be hard to filter by the Top 30%. Additionally, notice that Brian Stugart has a 30.01 Sales Rank %; he was placed in the mid 40 % in this method. He will be included in the Top 30% in the next couple of methods.
2nd Method - Parameters
In this second method I found the 30/40/30 split with the use of parameters and one calculated field.
Calculations & Parameters
Top 30% of Cust Sales (Parameter 1)
Bottom 30% of Cust Sales (Parameter 2)
Duplicate the parameter above but enter .7 for the current value.
Sales Percentile Label
IF RANK_PERCENTILE(SUM([Sales])) >= (1-[Top 30% of Cust Sales ]) then "Top30%"
ELSEIF RANK_PERCENTILE(SUM([Sales])) <= (1-[Bottom 30% of Cust Sales ]) then "Bottom30%"
ELSE "Mid 40%"
END
How to Create the Worksheet
Place Customer Name on the Rows shelf.
Place Sales on the Columns shelf
Duplicate this pill by clicking on it and selecting the Ctrl key.
Place the duplicate pill on the rows shelf
Right click on the second Sales pill and select Dual Axis.
Synchronize the axis.
Change the second Marks card to a Circle to create a lollipop chart.
Change the size of the circle to be larger than the bar or change the size of the bar to be smaller than the circle.
Mark the Bar gray.
Place the Sales Percentile Label on the second Sales Marks card in order to change the color of the circles.
Change the color of the circles to red, gray and blue.
Your worksheet should appear as below;
Issues with this Method
Since this chart was created using Parameters, you can filter the worksheet by the Top 30, Middle 40 and Bottom 30. The only downside to this method, is that you cannot get a count of the 30, 40, 30.
3rd Method - LOD Calculations
In this last method I use LOD calculations. First you need to find the Total sales by Customer. I do this with a Fixed LOD expression. Then you need to find the customers in the different percentiles. I do this with a combination of IF ELSEIF statement and PERCENTILE calculations.
Calculations
LOD Sales by Customer
{ Fixed [Customer Name] : SUM([Sales])}
Cust % Classification
IF [LOD Sales by Customer] >= {PERCENTILE([LOD Sales by Customer],.70)} then "Top 30%"
ELSEIF [LOD Sales by Customer] >= {PERCENTILE([LOD Sales by Customer],.30)} then "Mid 40%"
ELSEIF [LOD Sales by Customer] >= {PERCENTILE([LOD Sales by Customer],0)} then "Bottom 30%"
END
How to Create the Customer View Worksheet
Place Customer Name on the Rows shelf.
Place Sales on the Text Marks card.
Create an Index calculation on the Rows shelf next to Customer Name. (Make sure it's formatted as Discrete and the Table Calculation is Table Down.)
Place Customer % Classification to the Rows shelf.
Place this calculation on the Color Marks card as well.
Change the colors for the Top 30 % to blue, Mid 40 % to gray and Bottom 30 % to red.
*If you want a label for the Sales value, you can right-click on the Columns shelf to create a New Calculation. Simply type in "Sales". You will have to hide the field label for this column.
Your worksheet should appear as below;
Because I used LOD calculations, I can now use a Count Distinct calculation for Customer Name to obtain the count of the Customers in each of the groups.
How to Create the Overview Worksheet
Place Customer Name on the Columns shelf.
Change it to a Count Distinct Measure by right-clicking on this pill.
Place Cust % Classification on the Rows shelf.
Place Sales on the Columns shelf.
Place Cust % Classification on the Color Marks card in the first CNTD Customer Name Marks card.
Change the colors for the Top 30 % to blue, Mid 40 % to gray and Bottom 30 % to red.
You can add labels and apply other formatting as needed.
Your worksheet should appear as below;
You can now see that there is an even split for the Top 30% and Bottom 30%.