What if you have a data set with over twenty-five Categories. What if you only wanted to display the values for the top 10 Categories and then move all the other values in a category called Other. Maybe you have a client that wants to flip from top 5 and then top 10. How can you get Tableau to dynamically sum the values for Other including a percentage of the total?
Here is an example;
The dashboard I created is here in Tableau Public.
How do you create this in Tableau? For the Dynamic Top filter I found the solution in this article; How Dynamically Display the Top N vs Other in a Play Fair Data by Ryan Sleeper. The second issue was how to calculate the percentage of the total once this method is applied.
Here are the steps to create the Bar chart;
First you will need to create a Parameter. You will need to make this an Integer data type. I selected a range of values and made my minimum 5 and my maximum 10.
Top N Parameter
Sub-Category Set
Next you will need to create a set on the value you want the top 10 for. In this example I selected Sub-Category since there are 17 sub-categories in the Sales Superstore data set. Ryan Sleeper used State for his example.
Right-click on the Sub-category in your Data pane and select Create > Set.
Then on the Create Set pop-up menu, you need to go to the Top tab.
Check the By field button, select Top N and then select Sales. Click OK.
Calculated Field for Parameter
Now you need to create the calculated field that will display "Other" for any Sub-Category that is not in the Top N Parameter.
Here is the calculation;
Top N vs Other
IF [Sub-Category Set] then [Sub-Category]
ELSE "Other"
END
If you download my workbook, I have placed these fields in their own folder for your to reference.
How to Create the Bar Chart
First place Top N vs Other on your Rows shelf.
Next place Sales on the Columns shelf.
I like to place the Sub-Category set on the Rows shelf before the Top N vs Other calculated field to sort the values and to call out the Other value by itself with Row Banding later.
Place Sales on your Text Marks card and format it as currency with no decimal places. I also like to Display the Units as Thousands with the K abbreviation.
Row Banding
In order to just have the Other field with a background color, I like to use Row Banding.
Right-click on the bar chart and select Format to get the Formatting Pane.
Click on the Bucket or the Format Shading icon.
Scroll down to Row Banding and move the Band size slider to the second notch.
You will also need to move the Level slider to the front.
This will add a light grey to the background for just the Other value.
I also removed the background lines, changed the fonts of the Sub-categories etc.
Percentage of Total Calculations
Working with Tableau and Table calculations is tricky for new users. You have to be aware of Tableau's Order of Operations. We are using a Set and a Top N in this dashboard.
One way of getting the percentage is to create a an Level of Detail (LOD) Expression and then using this expression in another calculation to get the percent.
FIXED SUM SALES
{FIXED : SUM([Sales])}
% of Total
SUM([Sales])/SUM([Fixed SUM SALES])
To add this % to the view, I created a custom calculation on the Row shelf. I right-clicked and selected New Calculation. I then entered MIN(-85000). I made this a dual axis and then synchronized the axis. In the new Marks card for this calculation I placed the % of Total on the Text Marks card. You may have to adjust the negative value for the text spacing not to overlap the bars.
Issues with the LOD Calculations
The % of Total will change with the Top N slider. For example, if you changed the Top N to 5, the Other % changes to 43.7% from 12.2% However, if you wanted to add any filters to this view, say you wanted to add Region, this calculation will not display the correct %. For example, if you selected Central Region, it will display 3.2% for Phones and not aggregating the percentages for the filtered view. If you wanted Tableau to recalculate the Sub-categories for the newly filtered view with all of the visible categories totaling 100% you will have to use a different method.
In order to fix this, you need to right click on the Region filter of the Using LOD sheet and Add it to Context. This forces Tableau to look at Region first. It will mimic the Table calc.
Place Sales on the Min Marks card and select Quick Table Calculation and then Percent of Total.
Feel free to download the Tableau Public dashboard to see how the dashboard is configured.
コメント