Tableau Percentage of Total [Complete Guide for Beginners]

In data analysis, numbers alone are not always easy to understand. Sometimes, showing data as a percentage makes it much clearer. This is where the percentage of total concept becomes very useful.

In the Tableau data visualization tool, you can easily convert your raw numbers into percentages. This helps you understand how much each part contributes to the overall total.

Recently, I created a stacked bar chart to show sales by product category for each region. In this chart, when I tried to show the percentage, it displayed the percentage of the running total rather than 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 the total in Tableau using four different methods.

What is the 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.

Suppose the total sales are ₹100,000 and Product A contributes ₹25,000. The percentage of the total for Product A will be 25%. This clearly shows how much Product A contributes compared to the entire dataset.

Why Use Percentage of Total in Tableau

The percentage of total makes the data easier to read and compare across categories. It helps users quickly identify top-performing segments without having to analyze raw numbers. It also improves dashboard clarity and decision-making.

Key Terms You Should Know

1. Dimension
   • A dimension is a categorical field such as Region, Category, or Product. It is used to divide data into different groups. Dimensions help break down data for better analysis.
2. Measure
   • A measure is a numeric field, such as Sales or Profit. It is used to perform calculations such as sums, averages, and percentages. Measures are the main values used in percentage calculations.
3. Table Calculation
   • Table calculation is a Tableau feature that performs calculations on data already present in the view. It works based on how data is displayed in the table. The percentage of the total is commonly calculated using table calculations.
4. LOD Expression
   • LOD (Level of Detail) expressions allow you to control the level at which calculations are performed. They help calculate percentages independent of the visualization. This gives more flexibility and accuracy.

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 visualization, we will create a stacked bar chart using the Tableau Superstore dataset.

1. To create the stacked bar chart, add ‘Region’ to ‘Columns‘ and ‘Sales’ to Rows.

2. Add the Category dimension to the Color card in the Marks section. This will stack the Categories for each region in a single bar.

3. 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 total sales for each category in a specific region.

4. 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.

5. Remove the Sales from the Label of Sales(1) axis and Category from the Color of Sales(2) axis.

6. Set the Marks type for the first axis as a Bar chart and for the second axis as a Gantt chart.

7. If the percentage values are not aligned properly at the top of the bars, then right-click on the axis and select ‘Synchronize 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.

1. Create a calculated field named 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.

2. To create the view, Category to Columns and Sales to Rows.

3. Now add the calculated field to the Text card in the Marks section. To display the values as percentages, click the calculated field in the label and select Format.

4. 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, such as Sub-Category, are present in the view.

1. 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 the total sales. Then it divides category sales by the overall total to get the percentage.

2. To create a view, add a Category and optionally a Sub-Category to Rows.

3. Add the calculated field to the Text or Tooltip card in the Marks section.

4. Change the calculated field format to Percentage to 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 sums aggregated values within a defined “window” of data. This method is useful when the table view is already aggregated.

1. 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.

2. To create the view, drag the Sub-Category to the Rows and the calculated field to the Text card in the Marks section.

3. Click on the dropdown of the calculated field in the Marks section and select Compute Using -> Table (down).

4. 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.

Frequently Asked Questions

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, Quick Table Calculation is the simplest, while EXCLUDE and FIXED LODs provide 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.

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

Bijay Kumar

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.