How to Add Percentiles in Tableau

Calculating percentiles in Tableau is one of the most practical ways to understand where your data points stand in relation to each other. Whether you're trying to identify top-performing sales reps, outliers in website session durations, or simply segment your customers, percentiles give you a clear benchmark. This article will walk you through several methods for adding percentiles in Tableau, from simple point-and-click options to more powerful calculated fields and visualizations.

What Are Percentiles and Why Do They Matter?

In simple terms, a percentile is a measure that indicates the value below which a given percentage of observations in a group falls. For example, if you score in the 90th percentile on a test, it means you performed better than 90% of the other test-takers.

In business analysis, this is incredibly useful for:

• Benchmarking Performance: See how an individual product, region, or marketing campaign performs relative to all others. The 50th percentile, also known as the median, gives you the true midpoint of your data, unaffected by extreme outliers.
• Identifying Outliers: Quickly spot the top 1% or bottom 5% of your data. This could be your most valuable customers (99th percentile of spend) or your slowest-loading website pages (95th percentile of load time).
• Segmentation: Group your data into meaningful buckets. A common use case is creating quartiles (25th, 50th, and 75th percentiles) to segment customers into low, medium, and high spending groups.

Understanding where a data point falls provides much more context than just looking at averages, which can be easily skewed by unusually high or low values.

Method 1: The Quickest Way with Built-in Aggregations

Tableau has a built-in feature to calculate percentiles directly from the view. This is the fastest method and perfect for quick, exploratory analysis. It requires no code or calculated fields.

Step-by-Step Instructions:

1. Drag the dimension you want to analyze (e.g., Customer Name or Product Name) onto the Detail card on the Marks shelf. This tells Tableau to look at each individual customer or product.
2. Drag the measure you want to calculate a percentile for (e.g., Sales) onto the Rows or Columns shelf. By default, it will show up as an aggregation, likely SUM(Sales).
3. Right-click on the measure pill (the green SUM(Sales) pill).
4. Hover over Measure to expand the list of aggregations.
5. Select Percentile from the list. A dialog box will appear.
6. Enter the percentile you want to calculate. For example, to find the 75th percentile, type "75".
7. Click OK.

Your view will now display the 75th percentile value for your sales data. This method is great for a quick look but isn't as reusable or flexible as creating a dedicated calculated field.

Method 2: Using the PERCENTILE() Calculated Function

For more control and reusability, a calculated field is the way to go. You can create a distinct measure for any percentile, allowing you to use it in other calculations, reference lines, or labels across your dashboard. Tableau's PERCENTILE() function makes this straightforward.

The syntax is simple:

PERCENTILE([Measure], Percentile Value)

Note that the Percentile Value must be a number between 0 and 1. For example, the 90th percentile would be represented by 0.90.

Example: Calculating the 90th Percentile of Sales

Step-by-Step Instructions:

1. Click the dropdown arrow in the Data pane and select Create Calculated Field.
2. Name your calculation something descriptive, like "P90 Sales".
3. In the formula editor, type the following:

PERCENTILE([Sales], 0.90)

1. Click OK. You now have a new measure in your Data pane called "P90 Sales" that you can drag into any view, just like any other measure.

Working with Quartiles

You can use this same method to calculate quartiles, which divide your data into four equal parts. This is great for segmentation.

• Q1 (First Quartile / 25th Percentile): Sales value below which 25% of the data falls.
• Median (Second Quartile / 50th Percentile): The exact middle point of your dataset.
• Q3 (Third Quartile / 75th Percentile): Sales value below which 75% of the data falls.

By creating calculated fields for each, you can easily build tables or charts that show the distribution of your data, such as a five-number summary (Min, Q1, Median, Q3, Max).

Method 3: Level of Detail (LOD) Expressions for Advanced Scenarios

What if your question is more complex? For example, you want to find the 95th percentile of total sales per customer and compare that value across regions. A simple PERCENTILE([Sales], 0.95) won't work because it calculates the percentile based on all the individual sales transactions, not the summed total for each customer.

This is where Level of Detail (LOD) expressions are essential. They allow you to specify the level of granularity for your calculation, regardless of what's in your current view.

Example: Finding the 95th Percentile Customer

Our goal is to first calculate the total sales for each customer, and then find the 95th percentile value from that list of customer totals.

Step-by-Step Instructions:

1. Create a new calculated field. Name it "P95 Customer Sales".
2. Use a FIXED LOD expression inside your PERCENTILE function:

PERCENTILE({FIXED [Customer Name] : SUM([Sales])}, 0.95)

1. Click OK.

Let's break down what this formula does:

• {FIXED [Customer Name] : SUM([Sales])}: This part of the formula first goes through your dataset and calculates the total sales for each customer. It returns a temporary list of values, where each value is one customer's total sales.
• PERCENTILE(..., 0.95): The outer PERCENTILE function then takes that list of customer totals and finds the 95th percentile value from it.

The resulting calculation gives you a single value: the sales total that a customer needs to reach to be considered in the top 5% of all buyers. You can now use this static value across different visualizations, such as adding it as a reference line in a chart showing sales by region.

Visualizing Percentiles for Greater Impact

Calculating percentiles is only half the battle, visualizing them makes the insights easy to understand. Here are three effective ways to visualize your percentile calculations in Tableau.

1. Box-and-Whisker Plots

Box plots are Tableau's native way of visualizing data distribution and quartiles. They automatically show the median (50th percentile), the interquartile range (the box containing the middle 50% of the data, between the 25th and 75th percentiles), and any outliers.

How to Create a Box Plot:

1. Place a dimension like Category or Region on the Columns shelf.
2. Place your measure, like Sales, on the Rows shelf.
3. Add a dimension that represents the level of detail for each point (e.g., Order ID) to the Detail card.
4. From the Show Me panel in the top right, select the Box-and-Whisker plot chart.

Tableau will automatically create a box plot, giving you an immediate visual summary of your data's spread for each category.

2. Reference Lines and Bands

Reference lines are perfect for adding benchmarks to your existing charts. You can use one of your percentile calculations to see how your data points compare.

How to Add a Percentile Reference Line:

1. Create a chart, like a histogram of Customer Sales.
2. Right-click on the axis of your measure (e.g., the Sales axis).
3. Select Add Reference Line.
4. In the dialog box, set the Scope to Entire Table.
5. Under Line Value, you can select the Percentile option and have Tableau calculate it, or you can select your pre-built calculated field (e.g., "P90 Sales").
6. Customize the label and formatting, then click OK.

You can also create a reference band between two values, such as using your Q1 and Q3 calculated fields to highlight the middle 50% of your data.

3. Conditional Formatting in Tables

Use a percentile to highlight specific rows in a text table. For example, let's highlight top-tier customers who are above the 90th percentile.

How to set up highlighting:

1. First, make sure you have your "P90 Sales" calculated field from Method 2.
2. Create a new calculated field named "Is Top 10% Customer?".
3. Enter the formula:

SUM([Sales]) > [P90 Sales]

1. This will return True if a customer's total sales are greater than the 90th percentile, and False otherwise.
2. Build a table with Customer Name and SUM(Sales).
3. Drag your new calculated field, "Is Top 10% Customer?," onto the Color card.
4. Tableau will assign different colors to "True" and "False," instantly highlighting your top customers.

Final Thoughts

Mastering percentiles in Tableau takes your analysis from simply reporting what happened to understanding how it fits into the bigger picture. We've walked through the built-in feature for quick analysis, calculated fields for more flexibility, LODs for complex questions, and several ways to visualize these powerful benchmarks.

While Tableau is an amazing tool for deep analysis, we know that building LOD expressions and mastering its interface can be time-consuming, especially for busy teams. At Graphed, our goal is to eliminate that friction. Instead of writing formulas, you can simply ask in plain language, "Show me our top 10% customers by sales" or "What is the median order value for each marketing channel?" We then automatically build the live dashboard you need, connected directly to your sources, in seconds - not hours.