How to Calculate a Percentage in Tableau: The Top 5 Methods


Calculating percentages is a common task when exploring a dataset. When presenting your findings, you’ll often want to show some measures as percentages. They make comparisons easy and let you communicate your findings more effectively.

To calculate percentages in Tableau, you can use the “Quick Table Calculation” feature by right-clicking a measure and selecting “Percent of Total.” You can calculate percentages using calculated fields, windows functions, LOD expressions, and table calculations.

But, there is more than one way to do things here!

Read on to learn how to calculate percentages using various techniques.

These methods will easily adapt to your specific data requirements allowing you to create visualizations that present your results in a clear manner.

So, let’s dive into the world of Tableau and master the art of calculating percentages.

How to Calculate a Percentage in Tableau

5 Methods For Calculating Percentages in Tableau

In this section, we will review the top 5 methods for calculating percentages in Tableau.

Specifically, we will go over the following:

  1. Using Quick Table calculation
  2. Using Calculated Field
  3. Using Window Functions
  4. Using LOD Expressions
  5. Using Table Calculation
5 Methods For Calculating Percentages in Tableau

1. How to Calculate Percentages Using Quick Table Calculation

One of the most convenient ways of calculating percentages in Tableau is by using the quick table calculation.

To calculate percentages using quick table calculation, you can follow the steps given below:

Step 1: Add Dimension And Measure to The View

Drag and drop the dimension and measure you’re interested in (e.g., Sales) onto the appropriate shelf, typically the Columns or Rows shelf.

Adding dimension and measure to the view

Step 2: Initiate Quick Table Calculation

Right-click on the measure pill you’ve placed in the view and navigate to the “Quick Table Calculation” option in the dropdown menu.

Then select Percent of Total.

Initiating quick table calculation

When you select the Percent of Total, you will see the percentage in terms of grand totals for each category.

Sales displayed as percetages

2. How to Calculate Percentages Using Calculated Field

To calculate percentages using a calculated field, you can follow the steps given below:

Step 1: Open the Calculated Field Dialog

Navigate to Analysis > Create Calculated Field, or right-click in the “Data” pane and select “Create Calculated Field”.

Opening calculated field

Step 2: Name the Calculated Field

Give your new field a meaningful name, like “Sales Percentage”.

Naming the calculated field

Step 3: Enter the Formula

Depending on what percentage you want to calculate, you’ll enter the appropriate formula.

SUM([Sales]) / TOTAL(SUM([Sales]))

The above formula will calculate the percentage.

Calculating percentage

Once you’ve entered your formula, click “OK” to close the dialog and add the calculated field to your data pane.

Step 4: Drag the Calculated Field to Your View

Drag the new “Sales Percentage” field (or whatever you named it) onto the desired shelf.

Dragging calculated field to view

Step 5: Format The Values

After carrying out all your calculations, you must ensure that the values are displayed as percentages.

Right-click on the calculated field pill in your view, choose Format, and under the Numbers dropdown, select Percentage.

Formatting the values

3. How to Calculate Percentages Using Window Function

Using window functions in Tableau allows you to perform calculations across a specified range of rows related to the current row, offering dynamic ways to compute percentages based on your visualization.

Here’s how to calculate percentages using a window function:

Step 1: Open the Calculated Field Dialog

Navigate to Analysis > Create Calculated Field, or right-click in the “Data” pane and select Create Calculated Field.

Opening calculated field

Step 2: Name The Calculated Field And Enter Formula

After opening the calculated field dialog box, you’ll enter the appropriate formula.

To compute the percentage of total sales, you can use the following formula:

SUM([Sales]) / WINDOW_SUM(SUM([Sales]))

Click OK after entering the formula.

Creating calulated field

Step 3: Drag the Calculated Field to Your View

Add your new Sales Percentage Window field (or whatever you named it) to the appropriate shelf.

Dragging the calculated field to view

Step 4: Formatting The Values

Right-click on the calculated field pill in your view, choose Format, and under the Numbers dropdown, select Percentage.

Formatting the values

4. How to Calculate a Percentage Using LOD Expressions

Level of Detail (LOD) expressions in Tableau enable you to perform calculations at specific levels of granularity.

You can use include, exclude, and fixed LOD calculations to compute percentages.

To calculate percentages using the LOD expressions, you can follow the steps given below:

Step 1: Open the Calculated Field Dialog

Navigate to Analysis > Create Calculated Field, or right-click in the Data pane and select Create Calculated Field.

Step 2: Name the Calculated Field And Enter The Formula

Label your calculated field by giving it an appropriate name.

Let’s say you have a view broken down by both Category and Sub-Category, but you want to calculate the percentage of sales for each Sub-Category relative to its Category, excluding the influence of any other dimensions.

You can use the following LOD expression:

{INCLUDE [Sub-Category]: SUM([Sales])} / {EXCLUDE [Sub-Category]: SUM([Sales])}

By using the {INCLUDE [Sub-Category]:…} expression, you’re ensuring that the numerator aggregates sales at the Sub-Category level of detail, just like the denominator.

Creating calculated field

Click OK and then add the calculated field to your view.

5. How to Calculate Percentages Using Table Calculations

Table Calculations are a powerful feature and can be used to calculate percentages across different dimensions and the entire table.

To create a table calculation, follow these steps:

Step 1:

Right-click on a measure in your view, and then select Add Table Calculation.

Adding table calculation

Step 2:

In the Table Calculation dialog box, choose a calculation type that suits your needs (e.g., Percent of Total, Running Total, or Difference).

Selecting Calculation Type

Once you have completed these steps, the percentage values will be displayed in your view.

Take a look at the below video to learn more about Advanced Data Analytics by OpenAI:

Final Thoughts

Calculating percentages in Tableau is a fundamental skill every user should master. Percentages offer a clearer perspective than raw numbers, making data more digestible and insights more actionable.

By understanding how to compute percentages, you ensure that your dashboards and reports are both informative and relatable. This knowledge enhances not only the accuracy of your work but also its relevance.

In a world driven by data, being able to present numbers in a context, like percentages, is invaluable. It aids in better decision-making and clearer communication.

Frequently Asked Questions

In this section, you will find some frequently asked questions you may have when calculating percentages in Tableau.

Data analyst going through an analytics report

How to compute the percentage of a total with filters applied?

To compute the percentage of a total with filters applied in Tableau, you should use Level of Detail (LOD) expressions.

For example, create a calculated field using LOD with the FIXED keyword:

SUM([Sales]) / SUM({FIXED : SUM([Sales])})

This will calculate the percentage considering all data, even after applying filters.

How to display a percentage over 100 in Tableau?

In order to display a percentage over 100 in Tableau, you can create a calculated field to manually compute the percentage.

Divide the value by the total, and multiply the result by 100:

(SUM([Value]) / SUM([Total])) * 100

Now, place this calculated field in your visualization to display the percentage, even if it exceeds 100%.

What is the method to convert the number to a percentage in a calculated field?

To convert a number to a percentage in a calculated field in Tableau, divide the value by the total and then multiply by 100.

For example:

(SUM([Value]) / SUM([Total])) * 100

This will provide the percentage value needed for your visualization.

How to calculate the percentage of a total for multiple measures?

If you need to calculate the percentage of a total for multiple measures, create a separate calculated field for each measure:

(SUM([Measure 1]) / SUM([Total])) * 100
(SUM([Measure 2]) / SUM([Total])) * 100

Now, place these calculated fields in your visualization to display the respective percentages for each measure.

What is the process for showing percentages in a bar chart?

To show the percentage in a bar chart, first create a calculated field to calculate the percentage:

(SUM([Value]) / SUM([Total])) * 100

Drag this calculated field to the Rows or Columns shelf, depending on whether you want a vertical or horizontal bar chart.

Then, place the desired dimension on the opposite shelf to create the bar chart with percentage values.

How do I format a value as a percentage in Tableau?

To format a value as a percentage in Tableau, right-click on the field that you want to format on the Rows or Columns shelf or in the view itself.

Choose ‘Format…’, and in the Format pane that appears, click on the Numbers dropdown menu under the Axis or Pane tab, and select ‘Percentage.’

Adjust the number of decimal places as needed.



Source link

Be the first to comment

Leave a Reply

Your email address will not be published.


*