Tableau Aggregate Functions
Aggregate functions in Tableau are used to perform calculations on groups of data rather than on individual data points. These functions can be used to perform various types of calculations such as sum, average, maximum, minimum, count, and many others. You can access these capacities by selecting Aggregate from the capacity drop-down list on the Create Calculated Field window.
Types of Aggregate Functions in Tableau
Let’s go over each of the Tableau Aggregate Functions one by one:
1. ATTR perform
ATTR is a function in Tableau that stands for “Attribute”. It is used to return the value of an attribute for a specific dimension in a data set. The ATTR function is typically used when we have a data set where there is only one value for a particular dimension and we want to include that value in our analysis.
For example, let’s say we have a dataset containing information about employees, including their name, department, salary, and job title. We want to create a visualization that shows the average salary for each department, along with the job title of the employee with the highest salary in each department.
To do this, we can use the ATTR function to return the job title of the employee with the highest salary. Since there is only one job title per employee, the ATTR function will return the name of that job title. If there were multiple job titles for each employee, we would need to use a different function to return the desired value, such as MIN or MAX.
The ATTR function is particularly useful when working with data that has been aggregated in some way, such as with a SUM or AVG function. In these situations, we may want to include additional information about the data set, such as the name of the customer or the region where the data was collected. The ATTR function can be used to retrieve this information.
It’s important to note that the ATTR function will only work when there is exactly one value for a particular dimension. If there are multiple values, the ATTR function will return an error. In such cases, we may need to use a different function, such as MIN or MAX, to retrieve the desired value.
Syntax :
ATTR(expression)2. AVG perform
AVG is a function in Tableau that stands for “Average”. It is used to calculate the arithmetic mean of a set of values in a data set. The AVG function is typically used when we want to find the average value of a particular variable in our data.
For example, let’s say we have a dataset containing information about sales transactions, including the sales amount, the date of the transaction, and the sales representative responsible for the transaction. We want to create a visualization that shows the average sales amount per sales representative for a given period of time.
To do this, we can use the AVG function to calculate the average sales amount for each sales representative. We would first drag the “Sales Representative” field to the Rows shelf, and the “Sales Amount” field to the Marks card. We would then change the aggregation for the “Sales Amount” field to AVG.
The AVG function calculates the sum of all the values in the dataset and divides it by the number of values in the dataset. In this way, the AVG function provides us with a way to understand the average value of a particular variable in our data.
It’s important to note that the AVG function only works with numerical values. If we try to use the AVG function with non-numerical values, such as dates or text strings, Tableau will return an error. In such cases, we may need to use a different function, such as COUNT or SUM, to retrieve the desired value.
Syntax :
AVG(Expression)3. COUNT perform
COUNT and COUNTD are both functions used in Tableau to count the number of rows in a given dataset or a subset of the dataset. However, they have some important differences in how they operate.
COUNT:
Syntax :
COUNT([Sales]) WHERE [Region] = "West"In this example, we’re using the COUNT function to count the number of non-null values in the “Sales” column for all rows where the “Region” column is equal to “West”.
COUNTD:
The COUNTD function in Tableau is used to count the number of unique values in a given dataset or a subset of the dataset. For example, if you have a dataset of sales data and you want to count the number of unique customers who made a purchase in a specific region, you might use the COUNTD function like this:
Syntax :
COUNTD([Customer ID]) WHERE [Region] = "West"In this example, we’re using the COUNTD function to count the number of unique values in the “Customer ID” column for all rows where the “Region” column is equal to “West”.
It’s important to note that the COUNT function will count all non-null values, including duplicates, while the COUNTD function will only count unique values. So if you have duplicate values in your dataset, the COUNTD function will give you a lower count than the COUNT function.
4. MAX perform
The MAX aggregate function in Tableau is used to find the maximum value in a given field or expression. It is commonly used in data analysis and reporting to identify the highest value in a dataset.
When you use the MAX function in Tableau, it aggregates the data and returns a single value that represents the maximum value in the selected field or expression. This value can be used in various calculations, such as finding the highest sales figure in a particular region, or identifying the maximum temperature recorded in a certain time period.
To use the MAX function in Tableau, follow these steps:
1.Open the worksheet where you want to use the MAX function.
2.Drag the field or expression you want to find the maximum value for to the “Rows” or “Columns” shelf.
3.Right-click on the field or expression in the “Rows” or “Columns” shelf and select “Add Measure” > “MAX”.
Tableau will automatically create a new column with the maximum value of the selected field or expression for each group or category in your dataset.
Overall, the MAX function in Tableau is a useful tool for quickly identifying the highest value in a dataset, which can provide valuable insights and help with decision making.
Syntax :
MAX([Sales]) WHERE [Region] = "West"In this example, we’re using the MAX function to find the maximum value in the “Sales” column for all rows where the “Region” column is equal to “West”.
The MAX function can be used with any column in a dataset that contains numeric values, such as sales data, product prices, or customer ages. It can also be used with date or time columns to find the maximum date or time value.
It’s worth noting that the MAX function will only return one value, even if there are multiple rows in the dataset that have the same maximum value. If you need to find all rows that have the maximum value, you can use other functions in combination with the MAX function, such as the RANK or INDEX functions.
5. MEDIAN perform
The Median aggregate function in Tableau is used to calculate the median value of a selected field across multiple rows of data. To use the Median aggregate function, follow these steps:
1.Drag the field you want to find the median of onto the view.
2.Click on the drop-down arrow next to the field in the view.
3.Select “Measure” -> “Median”.
Tableau will then calculate the median of the selected field and display it in the view.
You can also use the Median aggregate function in combination with other functions or calculations to create more complex analyses.
6. MIN perform
In Tableau, MINI is a function that can be used to find the minimum value of a particular field or expression. It is commonly used in calculations and can be accessed through the “Functions” pane or by typing “MINI” into a calculated field.
To use MINI in Tableau, follow these steps:
1 .Open the worksheet where you want to use the MINI function.
2. Click on the drop-down arrow next to the field or expression you want to find the minimum of.
3.Select “Measure” and then “MINI” from the list of functions.
Tableau will automatically create a new column with the minimum value of the selected field or expression for each row in your dataset.
Alternatively, you can use the MINI function in a calculated field by typing “MINI()” followed by the field or expression you want to find the minimum of, and closing with a parenthesis. For example, if you wanted to find the minimum value of the “Sales” field, you would type “MINI(Sales)” in the calculated field.
Overall, MINI is a useful function in Tableau for finding the smallest value in a dataset or subset of data, and can be used in a variety of analysis and reporting scenarios.
Syntax :
MIN(expression) MIN([Age]) = one
7. STDEV perform
STDEV is a function that is used to calculate the standard deviation of a given field or expression. The STDEV function is commonly used in statistical analysis to understand the variability of a dataset.
To use the STDEV function in Tableau, follow these steps:
1.Open the worksheet where you want to use the STDEV function.
2.Click on the drop-down arrow next to the field or expression you want to calculate the standard deviation for.
3.Select “Measure” and then “STDEV” from the list of available functions.
Tableau will automatically create a new column with the standard deviation of the selected field or expression for each row in your dataset.
Alternatively, you can use the STDEV function in a calculated field by typing “STDEV(” followed by the field or expression you want to calculate the standard deviation for, and closing with a parenthesis. For example, if you wanted to calculate the standard deviation of the “Sales” field, you would type “STDEV(Sales)” in the calculated field.
The resulting output will show you the standard deviation of the selected field or expression, which is a measure of the spread or variability of the data. The higher the standard deviation, the more spread out the data is. The STDEV function is useful for identifying outliers, understanding the distribution of a dataset, and making predictions based on historical data.
Syntax :
STDEV(expression) Aggregate Functions
8. STDEVP perform
The STDEVP function in Tableau is similar to the mathematical variance function, but it calculates the variance for all values in a given expression based on a biased population. This means that it takes into account all values in the population, rather than just a sample, which can result in a slightly different calculation compared to the variance function. Overall, the STDEVP function returns the statistical variance of a population, rather than just a sample.
Let’s say you have a dataset of sales figures for a retail company, and you want to calculate the population standard deviation of the sales for all stores. Here’s how you would use the STDEVP function:
Syntax:
STDEVP(expression) Open the worksheet where you want to use the STDEVP function.
1.Click on the drop-down arrow next to the “Marks” card and select “Measure”.
2.Select “STDEVP” from the list of available functions.
3.In the formula editor, select the field or expression for which you want to calculate the standard deviation. For example, you could select the “Sales” field.
4.Click “OK” to save the calculated field.
Tableau will automatically create a new column with the population standard deviation of the selected field for each row in your dataset.
The resulting output will show you the population standard deviation for all of the sales values in your dataset, which can help you better understand the spread and variability of the data.
9. SUM perform
SUM is a function that is used to calculate the sum of values in a given field or expression. The SUM function is commonly used in calculations and can be accessed through the “Functions” pane or by typing “SUM” into a calculated field.
To use the SUM function in Tableau, follow these steps:
1.Open the worksheet where you want to use the SUM function.
2.Click on the drop-down arrow next to the field or expression you want to sum.
3. Select “Measure” and then “SUM” from the list of available functions.
Tableau will automatically create a new column with the sum of the selected field or expression for each row in your dataset.
Alternatively, you can use the SUM function in a calculated field by typing “SUM(” followed by the field or expression you want to sum, and closing with a parenthesis. For example, if you wanted to sum the values in the “Sales” field, you would type “SUM(Sales)” in the calculated field.
Overall, the SUM function is a useful tool in Tableau for calculating the total value of a given field or expression.
Syntax :
SUM(expression)Example:
SUM(4,5,6) = 15Thus, these are the aggregate functions available in tableau.