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.