Tableau Aggregate Functions | Aggregate Functions and How to use

Introduction to Tableau Aggregate Functions

Tableau is not just a tool that makes graphs and reports, the optimum the data representation we do need the Functions in Tableau. By accessing this function just click the select aggregate from the column drop-down list to make the Create Calculated Field. Below are some of the Tableau Aggregate Functions list:

Number Functions
String Functions
Date Functions
Type Conversion Functions
Aggregate Functions
Logical Functions

From the above, we’ll see more detail about Aggregate Functions which is most commonly using.

Aggregate Functions

Aggregate functions are built-in functions that allow you to summarize or change the granularity of your data. Following are the various Aggregate functions in Tableau:

1. AVG

Average functions return the mean of all values in expression and can be used with numeric fields only.

(Note: it will ignore the NULL values)

Syntax: AVG (expression) | Example: AVG ([Sales]) = 186

2. ATTR

The functions only return the single value for the calculated row and the NULL values can be ignored. If the condition is not meet, it will return the asterisk value.

Syntax: ATTR (expresstion) | Example: ATTR ([country]) = India

3. COLLECT

COLLECT function can be used for spatial fields. It will combine the values in the argument fields and NULL values can be ignored.

Syntax: COLLECT (Spatial) Example: COLLECT ([Geometry])

4. CORR

CORR stands for correlation between the two give expressions. When we want to correlate between the two variables, it can return three values 1, 0 and -1. The values 1 represents the positive changes, 0 represents no relationship and -1 represents the negative change

Syntax: CORR (expr1, expr2)

5. COUNT

It will return the number of values present in the selected column and NULL values can’t be counted. Take the best example for this function, making a pivot table in excel by count the values-based category.

Syntax: COUNT (expression) | Example: COUNT ([‘Sales ID’)] = 45613

Popular Course in this category

Tableau Training (4 Courses, 6+ Projects)4 Online Courses | 6 Hands-on Projects | 39+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (3,956 ratings)

Course Price

View Course

6. COUNTD

It will return the distinct count for the given column or a group and it will not return a NULL value. These functions will be using the source of MS excel or text or Access.

Syntax: COUNTD (expression) | Example: COUNTD (‘Country’) = 256

7. COVAR

COVER stands for covariance for the given two expressions. The positive covariance indicates the variable is moving towards in the right direction and we can be noticed in the trend. The data can be considered as a random sample.

Syntax: COVAR (expr1, EXPR2)

8. COVARP

This function is similar to covariance but it only biased with the population.

Syntax: COVARP expr1, EXPR2)

9. MAX

The MAX functions return the maximum values for the given expression. Suppose we have to see the maximum age in the given voter list. We could apply this function to see it.

Syntax: MAX (expression) | Example: MAX ([Age]) = 105

10. MEDIAN

It will return the numeric values with the median of a single expression. It will return the middle value for the given expression it will ignore the NULL values.

Syntax: MEDIAN (expression) | Example: MEDIAN (1, 2, 3, 4, 5) = 3

11. MIN

The MIN functions return the minimum values for the given expression. Suppose we have to see the minimum sales rate in the store. We could apply this function to see it.

Syntax: MIN (expression) | Example: MIN ([Sales rate]) = 5

12. PERCENTILE

This function will return the percentile for the corresponding given expression. It only returns values between 0 and 1. Suppose we’ve given a decimal value like 0.54, it will return the median value.

Syntax: PERCENTILE (expression, number) | Example: PERCENTILE ([Sales], 0.54) = 37.69

13. STDEV

It stands for standard deviation function, it will returns if there are any high or low standard deviation values for the average value constrain

Syntax: STDEV(expression)

14. STDEVP

It is similar to the standard deviation but the difference is, it will be based on a population.

Syntax: STDEVP(expression)

15. SUM

We must all know that it is a simple function that calculates the total numbers for the given range of numeric values and it can be ignored NULL values.

Syntax: SUM (expression) | Example SUM (5, 5, 5) = 15

16. VAR

The VAR stands for variance in the given expression.

Syntax: VAR (expression)

17. VARP

It is similar to the Variance but the difference is, it will be based on a population.

Syntax: VARP(expression)

How does Aggregate Function Work in Tableau?

In detail, we’ll see how to use these functions in Tableau:

Open a new workbook in tableau desktop and connect the data sources using the .xls file dataset. Here the dataset we can use any store sales report and calculate the average sales on weekdays. We can simply find the average of sales by drag and drop the date into row field and then make sales in the second column as average (AVG), like below,

The problem by making AVG calculations in Tableau is based on each occurrence of the sales. But when we look deeper into the data, each sales having multiple no. of order and different products. The sample figure has been shown below,

Here we clearly understand the calculation made by the sum of sales and created an average. The Tableau automatically takes as SUM (Sales) / COUNT (Sales). Where we need to customize it to make the average for per day or weekdays, how do we make this? Yes, we need to use the calculated field to have AVG aggregation as SUM (Sales).

First, we need to create the daily sales total and then use this as a base to find the average sales for a day or weekdays. Then we can go to the worksheet to add this calculated field into the visualization. Add the filter to make a weekday and change the aggregation to AVG. Now we see the difference between how the Tableau default average values show and how the aggregate AVG value. The first weekday having only one sale occurrence, the next day shows the clear differences which having multiple orders with multiple products in the same sales.

Conclusion

Now in this article, we have learned what is about Tableau functions and its types, Aggregate Function with an example, how to use the aggregate function in Tableau desktop. Happy analysis!