How to Calculate Mode in Tableau

Calculating the mode in Tableau isn't as straightforward as finding an average with AVG() or a total with SUM(). While there’s no simple MODE() function to drag and drop, you can still easily find the most frequently occurring value in your dataset. This guide will walk you through exactly how to do it using the power of Level of Detail (LOD) expressions.

Why You Can't Just Type MODE() in Tableau

You might be wondering why a powerful analytics tool like Tableau omits a basic statistical function like mode. The main reason is that the mode can often be ambiguous and doesn't always fit neatly into Tableau's model of aggregating data for visualization.

Consider these scenarios:

• No Mode: If every value in a dataset appears only once, there is no mode.
• Multiple Modes (Bimodal/Multimodal): If two or more values are tied for the highest frequency, the dataset has multiple modes. How should a single function like [Mode([Category])] decide which one to show in a visualization? Should it show the first one it finds? All of them?

Because of this ambiguity, Tableau leaves the calculation up to you. This gives you more control and flexibility to define exactly how you want to find the mode and handle any ties. The best way to do this is by using Calculated Fields and LOD expressions.

Method 1: Finding the Overall Mode Using a FIXED LOD Expression

Let's start with the most common scenario: finding the single most frequent value across your entire dataset. For this example, we'll use the sample "Superstore" dataset that comes with Tableau and find the most common Sub-Category.

The logic is a two-step process:

1. First, we count how many times each Sub-Category appears.
2. Second, we figure out which Sub-Category has the highest of those counts.

We'll create two calculated fields to accomplish this.

Step 1: Count How Often Each Value Appears

First, we need to create a calculation that counts the number of records (or rows) for each sub-category. A FIXED LOD expression is perfect for this because it computes the value across the entire dataset, ignoring most filters in your view.

1. Open a new Calculated Field by navigating to Analysis > Create Calculated Field.
2. Name this field something clear, like [Sub-Category Count].
3. Enter the following formula:

{ FIXED [Sub-Category] : SUM([Number of Records]) }

Note: SUM([Number of Records]) can often be replaced with COUNT([Sub-Category]) or a similar count if that feels more intuitive. The result will be the same.

So, what does this formula do? The { FIXED [Sub-Category] : ... } part tells Tableau: "For every distinct Sub-Category, perform the aggregation I'm about to give you." The aggregation is SUM([Number of Records]), which simply counts the rows for that Sub-Category. The result is a value that is associated with each Sub-Category, representing its total frequency in the dataset.

Step 2: Identify the Sub-Category with the Maximum Count

Now that we have the count for every Sub-Category, we need to find the maximum of those counts and then return the name of the Sub-Category associated with it.

Create another new Calculated Field:

1. Name it [Overall Mode].
2. Enter this formula:

IF [Sub-Category Count] == { FIXED : MAX([Sub-Category Count]) } THEN [Sub-Category] END

Let’s break down this calculation:

• { FIXED : MAX([Sub-Category Count]) }: This is another LOD expression. Because there's no dimension specified before the colon, it calculates the expression across the entire dataset. It scans through our previously created [Sub-Category Count] field, finds the single highest value, and returns it. For the Superstore set, let's say "Binders" appears 1,523 times, which is the highest, so this expression will return the number 1523 for every row.
• IF [Sub-Category Count] == ... THEN [Sub-Category] END: The IF statement then checks each row. It compares the Sub-Category's individual count (e.g., Planes might be 777) to the overall maximum count (1523). If they match, the statement returns the name of the [Sub-Category]. If they don't, it returns NULL.

In the end, only the Sub-Category (or Sub-Categories, if there's a tie) that is the mode will have its name returned by this field. All others will be null.

Step 3: Putting It All Together in the View

Now you can use your new calculated field to display the mode.

1. Drag your [Overall Mode] calculated field to the Text card on the Marks shelf.
2. You'll likely see the name of your mode and also a "Null" value. To clean this up, drag [Overall Mode] again, this time onto the Filters shelf.
3. In the filter pop-up window, check the box to Exclude "Null" and click OK.

Voila! Your view is now cleanly displaying "Binders," the most frequently occurring Sub-Category in the entire dataset.

Method 2: Finding the Mode for Each Group or Category

Sometimes finding the overall mode isn't enough. A more powerful analysis is to find the mode within different groups. For example: what is the most popular Sub-Category sold within each Region?

The logic is very similar, but we just need to adjust our LOD expressions to be aware of the "Region."

Step 1: Count Occurrences for Each Sub-Category within Each Region

Instead of counting sub-categories for the whole dataset, we'll tell Tableau to count them and restart that count for each new region.

1. Create a Calculated Field named [Sub-Category Count per Region].
2. Enter the formula:

{ FIXED [Region], [Sub-Category] : SUM([Number of Records]) }

This FIXED LOD now includes [Region]. This tells Tableau to compute the count for each unique combination of Region and Sub-Category. For instance, it will calculate the count for ("Central", "Binders"), ("East", "Binders"), ("Central", "Art"), and so on.

Step 2: Isolate the Maximum Count for Each Main Group

Next, for each Region, we need to know what its own highest sub-category count was. Was it 200 in the West? 350 in the East? We don't want the overall max anymore, we want the max per Region.

1. Create a Calculated Field named [Max Count per Region].
2. Enter the formula:

{ FIXED [Region] : MAX([Sub-Category Count per Region]) }

Here, the LOD expression is fixed on just the [Region] dimension. It looks at all the counts from our previous calculated field ([Sub-Category Count per Region]) and finds the maximum value for each region separately.

Step 3: The Final Mode Calculation

Now we have all the pieces. We can compare each Sub-Category's count within a region to that region's maximum count.

1. Create a Calculated Field named [Mode Sub-Category per Region].
2. Enter the formula:

IF [Sub-Category Count per Region] == [Max Count per Region] THEN [Sub-Category] END

This IF statement works just like in our first method, but its context is now at the regional level. For a given row, it asks, "Is the count for this specific Sub-Category in this specific Region equal to the maximum Sub-Category count for this Region overall?" If yes, it returns the Sub-Category name.

Step 4: Visualizing the Mode for Each Region

Let's build a simple table to show the results clearly.

1. Drag the [Region] dimension to the Rows shelf.
2. Drag your new [Mode Sub-Category per Region] field to the Text card.
3. Again, you'll see Nulls. Drag [Mode Sub-Category per Region] to the Filters shelf and exclude the Null values.

Your view will now display a clean list showing each region and its top-selling sub-category. You've successfully calculated the mode for each group in your view.

Important Consideration: What About Ties?

As mentioned earlier, it's possible for multiple values to tie for the most frequent spot. The methods described above will handle this by simply displaying all the values that are tied. For example, if "Binders" and "Paper" both had the highest count in the Central region, your "Mode Sub-Category per Region" field would return both names for that region.

This is often the desired behavior, as it accurately reflects your data. It shows you there isn't one single winner. However, if your use case demands that only one mode is ever shown, you would need to add another layer of logic to your final calculation to decide the tie-breaker (e.g., picking the Sub-Category that comes first alphabetically using a MIN() aggregation).

For most analyses, though, simply being aware that ties are visible is enough.

Final Thoughts

While Tableau doesn't have a direct MODE() function, an understanding of Level of Detail expressions gives you the power to find the most frequent occurrences in your data. By first counting items with a FIXED LOD and then finding the maximum of that count, you can pinpoint the mode for your entire dataset or for specific segments within it.

We know that creating nested Level of Detail formulas and debugging nulls can take time away from actually analyzing your data. That’s precisely why we built Graphed. Instead of writing multi-step formulas, you could just connect your data and ask, "Show me the top product subcategory for each region by sales count." Graphed speaks plain English and uses AI to generate charts, reports, and real-time dashboards for you, turning hours of report building into seconds.