Recently, I created a stacked bar chart to show the sales of product categories for each region. In this chart, when I tried to show the percentage, it was showing the percentage of the running total instead of the percentage of the total.
Then, using the quick table calculation, I was able to display the percentage of total on the Tableau stacked bar charts. Along with this, I also found other ways through which we can calculate the percentage of total, which use WINDOWS_SUM, INCLUDE LOD and EXCLUDE LOD.
In this tutorial, I will explain how to calculate the percentage of total in Tableau with the help of four different methods.
Percentage of Total in Tableau
In Tableau, the percentage of total calculates the proportion each part contributes to the whole by dividing a measure’s value by the sum of that measure for all parts. We can apply this quickly using Tableau’s Quick Table Calculation feature, or create custom calculations with Level of Detail (LOD) expressions like FIXED or EXCLUDE and also using the WINDOWS_SUM.
Calculate Percentage of Total in Tableau
In the examples below, we will calculate the Percentage of the Total in Tableau using the methods below.
- Calculate the percentage of the total using Quick Table
- Calculate the percentage of the total using EXCLUDE LOD
- Calculate the percentage of the total using FIXED LOD
- Calculate the percentage of the total using WINDOWS_SUM
Calculate the Percentage of Total Using Quick Table Calculation
In this example, we will calculate the percentage of the total using the quick table calculation.
In this data visualisation, we will create a stacked bar chart using the Tableau Superstore dataset.
- To create the stacked bar chart, add ‘Region’ to ‘Columns‘ and ‘Sales’ to Rows.
- Add the Category dimension to the Colour card in the Marks section. This will stack the Categories for each region in a single bar.
- Add the Sales to the Text card in the Marks section. After this, click on the Sales and select Quick Table Calculation -> Percent of Total.
Now, we can see the percentage of the total for the share of sales of each category in a specific region.
- In this, we can also show the overall total at the top of the bar instead of showing it for each stacked measure. To do this, duplicate the Sales in the Rows and create a Dual Axis chart.
- Remove the Sales from the Label of Sales(1) axis and Category from the Colour of Sales(2) axis.
- Set the Marks type for the first axis as a Bar chart and for the second axis as a Gantt chart.
- If the percentage values are not aligned properly at the top of the bars, then right-click on the axis and select ‘Synchronise Axis‘.
Now, we can get the percentage of the total for all stacked dimensions at the top of the bars.
This way, we can calculate the percentage of the total in Tableau by using the quick table calculation.
Calculate the Percentage of Total Using EXCLUDE LOD
In this method, we will calculate the percentage of the total using the EXCLUDE LOD in Tableau. This will display each category’s sales as a percentage of the overall total, ignoring Category.
Now follow the steps below to calculate the percentage of total using the EXCLUDE LOD in Tableau.
- Create a calculated field and label it as Sales % of Total (EXCLUDE), then enter the formula below.
SUM([Sales]) / SUM({ EXCLUDE [Category] : SUM([Sales]) })In this formula, { EXCLUDE [Category]: SUM([Sales])} gives the total sales excluding the category. Then divide the category’s sales by this total.
- To create the view, Category to Columns and Sales to Rows.
- Now add the calculated field to the Text card in the Marks section. To show the values in percentage, click on the calculated field in the label and select Format.
- In the Format, go to the Default section and select Numbers -> Percentage. Here, we can also select the Decimal places for the percentage values.
This way, we can calculate the percentage of the total using the EXCLUDE LOD in a calculated field.
Calculate the Percentage of Total Using FIXED LOD
In this example, we will calculate the percentage of the total using the FIXED LOD in a calculated field.
For this method, we will show each category’s sales as a percentage of the grand total, even when other dimensions like Sub-Category are there in the view.
- First, create a calculated field, Sales % of Total (FIXED) and enter the formula below.
SUM([Sales]) / SUM({ FIXED : SUM([Sales])})In the formula above, { FIXED: SUM([Sales]) } locks the calculation to the entire dataset (fixed across all dimensions) and returns total sales. Then it divides category sales by this overall total to get the percentage of the total.
- To create a view, add a Category and optionally a Sub-Category to Rows.
- Add the calculated field to the Text or Tooltip card in the Marks section.
- Change the calculated field format to Percentage, and it will display the percentage of the total for each sub-category.
This way, we can calculate the percentage of the total for the dimensions using the FIXED LOD in Tableau.
Calculate Percentage of Total Using WINDOWS_SUM
In this method, we will calculate the percentage of the total using the WINDOWS_SUM in Tableau.
The WINDOW_SUM is a table calculation function that is used to sum aggregated values within a defined “window” of data. This method is useful when the table view is already aggregated.
- Create a calculated field, Sales % of Total (WINDOWS_SUM), and enter the following formula.
SUM([Sales]) / WINDOW_SUM(SUM([Sales]))In the above formula, WINDOW_SUM(SUM([Sales])) calculates the sum of all rows in the current table window.
- To create the view, drag the Sub-Category to the Rows and the calculated field to the Text card in the Marks section.
- Click on the dropdown of the calculated field in the Marks section and select Compute Using -> Table (down).
- Change the calculated format to a percentage. For this, click on the calculated field and select Format. In the format, select Number -> Percentage.
With this, we can see the percentage of the total for the Sales percentage share of Sub-Categories. This way, we can calculate the percentage of the total using the WINDOWS_SUM function.
Conclusion
In this Tableau tutorial, we have learned different ways to calculate the percentage of total in Tableau using Quick Table Calculation, EXCLUDE LOD, FIXED LOD, and WINDOW_SUM.
In the above methods, the Quick Table Calculation is the simplest way, while EXCLUDE and FIXED LODs give more control for consistent totals regardless of the view. The WINDOW_SUM method is a table calculation useful for aggregated views.
By choosing any of the above methods, we can calculate and display the percentage of the total in Tableau data visualisation.
You may also like to read:
- Count Distinct Values in Tableau
- Tableau LOOKUP() Function
- Create a Drill Down Table in Tableau
- Convert Data Type in Tableau
I am Bijay Kumar, the founder of SalesforceFAQs.com. Having over 10 years of experience working in salesforce technologies for clients across the world (Canada, Australia, United States, United Kingdom, New Zealand, etc.). I am a certified salesforce administrator and expert with experience in developing salesforce applications and projects. My goal is to make it easy for people to learn and use salesforce technologies by providing simple and easy-to-understand solutions. Check out the complete profile on About us.
