When analysing sales performance in Tableau, I generally use the SUM() and AVG() functions to find the total and average values. But sometimes showing the average and sum is not sufficient for accuracy when the data includes values much higher or much lower than the rest.
When the dataset has these outliers, we should prefer using the Mean, Median, or Mode for the field values in Tableau.
In this Tableau tutorial, I will explain what the mean, median, and mode are in Tableau and how to use them for field-value calculations.
What are Mean, Median and Mode in Tableau?
In Tableau, measures like mean, median, and mode allow us to analyse and summarise our data. These metrics are essential for understanding trends and patterns in the datasets. By using Tableau’s built-in functions or custom calculations, we can calculate these values to create data visualisations.
Mean: The mean is the simple average of a set of numbers. It adds all the values and divides by the number of values. In Tableau, we can calculate the mean using the built-in AVG() function.
For example, if we add a measure like “Sales” to a view and change it to Average, Tableau shows the mean sales value across your data. However, if there are a few very high or very low sales values (outliers), they can affect the average and make it less accurate.
Mode: In a dataset, modes are the values that appear more frequently. A dataset can have no mode, one mode, or multiple modes if there are equal values.
In Tableau, we don’t have a direct MODE() function, but we can calculate it using custom calculations, like Level of Detail (LOD) expressions or table calculations.
For example, we can use a formula like {FIXED [Dimension]: COUNTD([Measure])} combined with ranking to identify the most common value.
Median: The median is the middle value in a list of numbers when they’re sorted from smallest to largest. If there are an even number of values, it’s the average of the two middle values.
In Tableau, we use the MEDIAN() function to calculate the median of a set of values. It is useful when we have outliers in a dataset because it ignores very high or very low values and shows the centre points. We can use this function in a calculated field or by changing the chart’s aggregation.
Calculate Mean, Median and Mode in Tableau
In the examples below, we will discuss the use cases of the aggregate functions mean, median, and mode in different scenarios in Tableau.
For the calculation and data visualisation, we will use the Tableau Superstore dataset. Now connect Tableau to the Superstore dataset and follow the example to calculate the mean, median, and mode.
Example 1: Calculate Mean in Tableau
In this example, we will see the method to calculate the Mean (Average) of the product Sub-Category sales.
- To create the view, add Sub-Category to Rows, and Sales to the Columns or Text card in Marks.
- By default, Tableau applies the SUM() aggregation. To change it to mean (average), click the dropdown on SUM(Sales) -> select Measure -> Average.
Now, we can see the average (Mean) calculation for each sub-category.
- To calculate the overall mean (average), click on the Analysis tab and select Totals -> Show Column Grand Totals.
With this, we can see the overall average of the measure values.
As an alternate approach, we can also create a calculated field using the formula below.
AVG([Sales])This shows the average sales value for each sub-category. This way, we can calculate the mean (average) in Tableau using the Avg() aggregate function.
Example 2: Calculate Median in Tableau
In Tableau, the Median is the middle value of the dataset. It gives a more accurate mid value than average when there are high or low values in the dataset, so it is used when the data isn’t evenly distributed.
In this example, we will calculate the median of the Sub-Category sales.
- To create the view, drag the Sales to the Text card and Sub-Categories to the Columns.
- In Tableau, we have built-in measures to calculate Median, such as the average calculation. For this, click on the Sales in the Text card and select Measure -> Median.
With this, we can see the median sales value for all sub-categories.
- For advanced calculations, we need to calculate the median of product sales profit, excluding products with negative sales or losses. For this requirement, we can use the formula below.
MEDIAN(
IF [Profit] > 0 THEN [Profit]
END
)- For the data visualisation, add the calculated field to the Text card. With this, we will see the median profit excluding products with negative profit values.
This way, we can calculate the median in Tableau using the built-in measure and a custom calculated field.
Example 3: Calculate the Mode in Tableau
In this example, we will calculate the Mode to find the most often used discount value in the Region. In Tableau, we don’t have any built-in measures to calculate the mode, so we will calculate it using a custom calculated field.
- Create a new calculated field and name it Discount Value – Occurrence, then enter the following formula:
{ FIXED [Region], [Discount] : COUNT([Discount]) }This calculation counts how many times each Discount value occurs in each Region.
- Now create another calculated field called Max Occurrence per Region, and use this formula:
{ FIXED [Region] : MAX([Discount Value – Occurrence]) }This formula will find the most repeated discount value for each region.
- As an alternative, we can also create a nested calculation using the above calculations, instead of calculating separately. For the nested calculation, use the formula below.
{ FIXED [Region] : MAX(
{ FIXED [Region], [Discount] : COUNT([Discount])}
) }- At last, create a calculated field, Mode Discount, using the formula below. This will return the Discount value whenever its count equals the maximum occurrence for that region.
IF [Discount Value – Occurrence] = [Max Occurrence per Region]
THEN [Discount]
END- To create the view, add a Region to the Rows and Mode Discount to the Text card.
With this, we can see which Discount values are most commonly used in each region.
We can also add other dimensions, like showing discount value mode for the Sub-Category in each region. For this, add the Sub-Category to the Rows near the Region dimension.
In this, we can see the discount value used frequently for sub-categories in each region.
This way, we can calculate the Mode values in Tableau by using a custom calculation.
Conclusion
In this Tableau tutorial, we have learn how to calculate and use the Mean, Median and Mode in Tableau. In the above examples, we have learned how to calculate the mean and median using the built-in Measure and also using the custom formula.
For the Mode calculation, there was no built-in option in Tableau, so we used a custom calculation. Using the custom calculation, we displayed the most used discount value for each region and also for the sub-category in each region.
You may also like to read:
- Pivot Columns in Tableau
- Tableau ZN() Function
- Tableau SPLIT() Function
- Aggregate Functions 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.
