- Tableau Public Download
- Tableau Public Dashboards
- Tableau Online Sign In
- Tableau Cost Model
- Tableau Online Pricing
Subscriptions give you access to Tableau over a set period of time. Maintenance is included in the price of a subscription license, keeping you up-to-date and giving you access to Tableau’s ongoing product releases and support services at no additional cost. The initial licensing cost for Tableau Online is slightly higher to account for the additional infrastructure we use to host the service. However, many customers see a lower total cost of ownership with Tableau Online, because they no longer have to pay for hardware and maintenance expenses.
This article introduces table calculation functions and their uses in Tableau. It also demonstrates how to create a table calculation using the calculation editor.
Why use table calculation functions
Table calculation functions allow you to perform computations on values in a table.
For example, you can calculate the percent of total an individual sale is for the year, or for several years.
Table calculation functions available in Tableau
FIRST( )
Returns the number of rows from the current row to the first row in the partition. For example, the view below shows quarterly sales. When FIRST() is computed within the Date partition, the offset of the first row from the second row is -1.
Example
When the current row index is 3,
FIRST() = -2
.INDEX( )
Returns the index of the current row in the partition, without any sorting with regard to value. The first row index starts at 1. For example, the table below shows quarterly sales. When INDEX() is computed within the Date partition, the index of each row is 1, 2, 3, 4.., etc.
Example
For the third row in the partition,
INDEX() = 3
.LAST( )
Returns the number of rows from the current row to the last row in the partition. For example, the table below shows quarterly sales. When LAST() is computed within the Date partition, the offset of the last row from the second row is 5.
Example
When the current row index is 3 of 7,
LAST() = 4
.LOOKUP(expression, [offset])
Returns the value of the expression in a target row, specified as a relative offset from the current row. Use FIRST() + n and LAST() - n as part of your offset definition for a target relative to the first/last rows in the partition. If
offset
is omitted, the row to compare to can be set on the field menu. This function returns NULL if the target row cannot be determined.The view below shows quarterly sales. When
LOOKUP (SUM(Sales), 2)
is computed within the Date partition, each row shows the sales value from 2 quarters into the future.Example
LOOKUP(SUM([Profit]), FIRST()+2)
computes the SUM(Profit) in the third row of the partition.MODEL_PERCENTILE(target_expression, predictor_expression(s))
Returns the probability (between 0 and 1) of the expected value being less than or equal to the observed mark, defined by the target expression and other predictors. This is the Posterior Predictive Distribution Function, also known as the Cumulative Distribution Function (CDF).
This function is the inverse of MODEL_QUANTILE. For information on predictive modeling functions, see How Predictive Modeling Functions Work in Tableau.
Example
The following formula returns the quantile of the mark for sum of sales, adjusted for count of orders.
MODEL_PERCENTILE(SUM([Sales]), COUNT([Orders]))
MODEL_QUANTILE(quantile, target_expression, predictor_expression(s))
Returns a target numeric value within the probable range defined by the target expression and other predictors, at a specified quantile. This is the Posterior Predictive Quantile.
This function is the inverse of MODEL_PERCENTILE. For information on predictive modeling functions, see How Predictive Modeling Functions Work in Tableau.
Example
The following formula returns the median (0.5) predicted sum of sales, adjusted for count of orders.
MODEL_QUANTILE(0.5, SUM([Sales]), COUNT([Orders]))
PREVIOUS_VALUE(expression)
Returns the value of this calculation in the previous row. Returns the given expression if the current row is the first row of the partition.
Example
SUM([Profit]) * PREVIOUS_VALUE(1)
computes the running product of SUM(Profit).RANK(expression, ['asc' | 'desc'])
Returns the standard competition rank for the current row in the partition. Identical values are assigned an identical rank. Use the optional
'asc' | 'desc'
argument to specify ascending or descending order. The default is descending.With this function, the set of values (6, 9, 9, 14) would be ranked (4, 2, 2, 1).
Nulls are ignored in ranking functions. They are not numbered and they do not count against the total number of records in percentile rank calculations.
For information on different ranking options, see Rank calculation.
Example
The following image shows the effect of the various ranking functions (RANK, RANK_DENSE, RANK_MODIFIED, RANK_PERCENTILE, and RANK_UNIQUE) on a set of values. The data set contains information on 14 students (StudentA through StudentN); the Age column shows the current age of each student (all students are between 17 and 20 years of age). The remaining columns show the effect of each rank function on the set of age values, always assuming the default order (ascending or descending) for the function.
RANK_DENSE(expression, ['asc' | 'desc'])
Returns the dense rank for the current row in the partition. Identical values are assigned an identical rank, but no gaps are inserted into the number sequence. Use the optional
'asc' | 'desc'
argument to specify ascending or descending order. The default is descending.With this function, the set of values (6, 9, 9, 14) would be ranked (3, 2, 2, 1).
Nulls are ignored in ranking functions. They are not numbered and they do not count against the total number of records in percentile rank calculations.
For information on different ranking options, see Rank calculation.
RANK_MODIFIED(expression, ['asc' | 'desc'])
Returns the modified competition rank for the current row in the partition. Identical values are assigned an identical rank. Use the optional
'asc' | 'desc'
argument to specify ascending or descending order. The default is descending.With this function, the set of values (6, 9, 9, 14) would be ranked (4, 3, 3, 1).
Nulls are ignored in ranking functions. They are not numbered and they do not count against the total number of records in percentile rank calculations.
For information on different ranking options, see Rank calculation.
RANK_PERCENTILE(expression, ['asc' | 'desc'])
Returns the percentile rank for the current row in the partition. Use the optional
'asc' | 'desc'
argument to specify ascending or descending order. The default is ascending.With this function, the set of values (6, 9, 9, 14) would be ranked (0.00, 0.67, 0.67, 1.00).
Nulls are ignored in ranking functions. They are not numbered and they do not count against the total number of records in percentile rank calculations.
For information on different ranking options, see Rank calculation.
RANK_UNIQUE(expression, ['asc' | 'desc'])
Returns the unique rank for the current row in the partition. Identical values are assigned different ranks. Use the optional
'asc' | 'desc'
argument to specify ascending or descending order. The default is descending.With this function, the set of values (6, 9, 9, 14) would be ranked (4, 2, 3, 1).
Nulls are ignored in ranking functions. They are not numbered and they do not count against the total number of records in percentile rank calculations.
For information on different ranking options, see Rank calculation.
RUNNING_AVG(expression)
Returns the running average of the given expression, from the first row in the partition to the current row.
The view below shows quarterly sales. When
RUNNING_AVG(SUM([Sales])
is computed within the Date partition, the result is a running average of the sales values for each quarter.Example
RUNNING_AVG(SUM([Profit]))
computes the running average of SUM(Profit).RUNNING_COUNT(expression)
Returns the running count of the given expression, from the first row in the partition to the current row.
Example
RUNNING_COUNT(SUM([Profit]))
computes the running count of SUM(Profit).RUNNING_MAX(expression)
Returns the running maximum of the given expression, from the first row in the partition to the current row.
Example
RUNNING_MAX(SUM([Profit]))
computes the running maximum of SUM(Profit).RUNNING_MIN(expression)
Returns the running minimum of the given expression, from the first row in the partition to the current row.
Example
RUNNING_MIN(SUM([Profit]))
computes the running minimum of SUM(Profit).RUNNING_SUM(expression)
Returns the running sum of the given expression, from the first row in the partition to the current row.
Example
RUNNING_SUM(SUM([Profit]))
computes the running sum of SUM(Profit)SIZE()
Returns the number of rows in the partition. For example, the view below shows quarterly sales. Within the Date partition, there are seven rows so the Size() of the Date partition is 7.
Example
SIZE() = 5
when the current partition contains five rows.SCRIPT_BOOL
Returns a Boolean result from the specified expression. The expression is passed directly to a running analytics extension service instance.
In R expressions, use .argn (with a leading period) to reference parameters (.arg1, .arg2, etc.).
In Python expressions, use _argn (with a leading underscore).
Examples
In this R example, .arg1 is equal to SUM([Profit]):
SCRIPT_BOOL('is.finite(.arg1)', SUM([Profit]))
The next example returns True for store IDs in Washington state, and False otherwise. This example could be the definition for a calculated field titled IsStoreInWA.
SCRIPT_BOOL('grepl('.*_WA', .arg1, perl=TRUE)',ATTR([Store ID]))
A command for Python would take this form:
SCRIPT_BOOL('return map(lambda x : x > 0, _arg1)', SUM([Profit]))
SCRIPT_INT
Returns an integer result from the specified expression. The expression is passed directly to a running analytics extension service instance.
In R expressions, use .argn (with a leading period) to reference parameters (.arg1, .arg2, etc.)
In Python expressions, use _argn (with a leading underscore).
Examples
In this R example, .arg1 is equal to SUM([Profit]):
SCRIPT_INT('is.finite(.arg1)', SUM([Profit]))
In the next example, k-means clustering is used to create three clusters:
SCRIPT_INT('result <- kmeans(data.frame(.arg1,.arg2,.arg3,.arg4), 3);result$cluster;', SUM([Petal length]), SUM([Petal width]),SUM([Sepal length]),SUM([Sepal width]))
A command for Python would take this form:
SCRIPT_INT('return map(lambda x : int(x * 5), _arg1)', SUM([Profit]))
SCRIPT_REAL
Returns a real result from the specified expression. The expression is passed directly to a running analytics extension service instance. In
R expressions, use .argn (with a leading period) to reference parameters (.arg1, .arg2, etc.)
In Python expressions, use _argn (with a leading underscore).
Examples
In this R example, .arg1 is equal to SUM([Profit]):
SCRIPT_REAL('is.finite(.arg1)', SUM([Profit]))
The next example converts temperature values from Celsius to Fahrenheit.
SCRIPT_REAL('library(udunits2);ud.convert(.arg1, 'celsius', 'degree_fahrenheit')',AVG([Temperature]))
A command for Python would take this form:
SCRIPT_REAL('return map(lambda x : x * 0.5, _arg1)', SUM([Profit]))
SCRIPT_STR
Returns a string result from the specified expression. The expression is passed directly to a running analytics extension service instance.
In R expressions, use .argn (with a leading period) to reference parameters (.arg1, .arg2, etc.)
In Python expressions, use _argn (with a leading underscore).
Examples
In this R example, .arg1 is equal to SUM([Profit]):
SCRIPT_STR('is.finite(.arg1)', SUM([Profit]))
The next example extracts a state abbreviation from a more complicated string (in the original form 13XSL_CA, A13_WA):
SCRIPT_STR('gsub('.*_', ', .arg1)',ATTR([Store ID]))
A command for Python would take this form:
SCRIPT_STR('return map(lambda x : x[:2], _arg1)', ATTR([Region]))
TOTAL(expression)
Returns the total for the given expression in a table calculation partition.
Example
Assume you are starting with this view:
You open the calculation editor and create a new field which you name Totality:
You then drop Totality on Text, to replace SUM(Sales). Your view changes such that it sums values based on the default Compute Using value:
This raises the question, What is the default Compute Using value? If you right-click (Control-click on a Mac) Totality in the Data pane and choose Edit, there is now an additional bit of information available:
The default Compute Using value is Table (Across). The result is that Totality is summing the values across each row of your table. Thus, the value that you see across each row is the sum of the values from the original version of the table.
The values in the 2011/Q1 row in the original table were $8601, $6579, $44262, and $15006. The values in the table after Totality replaces SUM(Sales) are all $74,448, which is the sum of the four original values.
Notice the triangle next to Totality after you drop it on Text:
This indicates that this field is using a table calculation. You can right-click the field and choose Edit Table Calculation to redirect your function to a different Compute Using value. For example, you could set it to Table (Down). In that case, your table would look like this:
WINDOW_AVG(expression, [start, end])
Returns the average of the expression within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
For example, the view below shows quarterly sales. A window average within the Date partition returns the average sales across all dates.
Example
WINDOW_AVG(SUM([Profit]), FIRST()+1, 0)
computes the average of SUM(Profit) from the second row to the current row.WINDOW_CORR(expression1, expression2, [start, end])
Returns the Pearson correlation coefficient of two expressions within the window. The window is defined as offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If start and end are omitted, the entire partition is used.
The Pearson correlation measures the linear relationship between two variables. Results range from -1 to +1 inclusive, where 1 denotes an exact positive linear relationship, as when a positive change in one variable implies a positive change of corresponding magnitude in the other, 0 denotes no linear relationship between the variance, and −1 is an exact negative relationship.
There is an equivalent aggregation fuction: CORR. See Tableau Functions (Alphabetical)(Link opens in a new window).
Example
The following formula returns the Pearson correlation of SUM(Profit) and SUM(Sales) from the five previous rows to the current row.
WINDOW_CORR(SUM[Profit]), SUM([Sales]), -5, 0)
WINDOW_COUNT(expression, [start, end])
Returns the count of the expression within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
Example
WINDOW_COUNT(SUM([Profit]), FIRST()+1, 0)
computes the count of SUM(Profit) from the second row to the current rowWINDOW_COVAR(expression1, expression2, [start, end])
Returns the sample covariance of two expressions within the window. The window is defined as offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end arguments are omitted, the window is the entire partition.
Sample covariance uses the number of non-null data points n - 1 to normalize the covariance calculation, rather than n, which is used by the population covariance (with the WINDOW_COVARP function). Sample covariance is the appropriate choice when the data is a random sample that is being used to estimate the covariance for a larger population.
There is an equivalent aggregation fuction: COVAR. See Tableau Functions (Alphabetical)(Link opens in a new window).
Example
The following formula returns the sample covariance of SUM(Profit) and SUM(Sales) from the two previous rows to the current row.
WINDOW_COVAR(SUM([Profit]), SUM([Sales]), -2, 0)
WINDOW_COVARP(expression1, expression2, [start, end])
Returns the population covariance of two expressions within the window. The window is defined as offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If start and end are omitted, the entire partition is used.
Population covariance is sample covariance multiplied by (n-1)/n, where n is the total number of non-null data points. Population covariance is the appropriate choice when there is data available for all items of interest as opposed to when there is only a random subset of items, in which case sample covariance (with the WINDOW_COVAR function) is appropriate. Ms teams forms bot.
There is an equivalent aggregation fuction: COVARP. Tableau Functions (Alphabetical)(Link opens in a new window).
Example
The following formula returns the population covariance of SUM(Profit) and SUM(Sales) from the two previous rows to the current row.
WINDOW_COVARP(SUM([Profit]), SUM([Sales]), -2, 0)
WINDOW_MEDIAN(expression, [start, end])
Returns the median of the expression within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
For example, the view below shows quarterly profit. A window median within the Date partition returns the median profit across all dates.
Example
WINDOW_MEDIAN(SUM([Profit]), FIRST()+1, 0)
computes the median of SUM(Profit) from the second row to the current row.WINDOW_MAX(expression, [start, end])
Returns the maximum of the expression within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
For example, the view below shows quarterly sales. A window maximum within the Date partition returns the maximum sales across all dates.
Example
WINDOW_MAX(SUM([Profit]), FIRST()+1, 0)
computes the maximum of SUM(Profit) from the second row to the current row.Tableau Public Download
WINDOW_MIN(expression, [start, end])
Returns the minimum of the expression within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
For example, the view below shows quarterly sales. A window minimum within the Date partition returns the minimum sales across all dates.
Example
WINDOW_MIN(SUM([Profit]), FIRST()+1, 0)
computes the minimum of SUM(Profit) from the second row to the current row.WINDOW_PERCENTILE(expression, number, [start, end])
Returns the value corresponding to the specified percentile within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
Example
WINDOW_PERCENTILE(SUM([Profit]), 0.75, -2, 0)
returns the 75th percentile for SUM(Profit) from the two previous rows to the current row.WINDOW_STDEV(expression, [start, end])
Returns the sample standard deviation of the expression within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
Example
WINDOW_STDEV(SUM([Profit]), FIRST()+1, 0)
computes the standard deviation of SUM(Profit) from the second row to the current row.WINDOW_STDEVP(expression, [start, end])
Returns the biased standard deviation of the expression within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
Example
WINDOW_STDEVP(SUM([Profit]), FIRST()+1, 0)
computes the standard deviation of SUM(Profit) from the second row to the current row.WINDOW_SUM(expression, [start, end])
Returns the sum of the expression within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
For example, the view below shows quarterly sales. A window sum computed within the Date partition returns the summation of sales across all quarters.
Example
WINDOW_SUM(SUM([Profit]), FIRST()+1, 0)
computes the sum of SUM(Profit) from the second row to the current row.WINDOW_VAR(expression, [start, end])
Returns the sample variance of the expression within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
Example
WINDOW_VAR((SUM([Profit])), FIRST()+1, 0)
computes the variance of SUM(Profit) from the second row to the current row.WINDOW_VARP(expression, [start, end])
Returns the biased variance of the expression within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
Example
WINDOW_VARP(SUM([Profit]), FIRST()+1, 0)
computes the variance of SUM(Profit) from the second row to the current row.Create a table calculation using the calculation editor
Follow along with the steps below to learn how to create a table calculation using the calculation editor.
Note: There are several ways to create table calculations in Tableau. This example demonstrates only one of those ways. For more information, see Transform Values with Table Calculations(Link opens in a new window).
Step 1: Create the visualization
- In Tableau Desktop, connect to the Sample-Superstore saved data source, which comes with Tableau.
- Navigate to a worksheet.
- From the Data pane, under Dimensions, drag Order Date to the Columns shelf.
- From the Data pane, under Dimensions, drag Sub-Category to the Rows shelf.
- From the Data pane, under Measures, drag Sales to Text on the Marks card.Your visualization updates to a text table.
Step 2: Create the table calculation
- Select Analysis > Create Calculated Field.
- In the calculation editor that opens, do the following:
- Name the calculated field, Running Sum of Profit.
- Enter the following formula:
RUNNING_SUM(SUM([Profit]))
This formula calculates the running sum of profit sales. It is computed across the entire table. - When finished, click OK.The new table calculation field appears under Measures in the Data pane. Just like your other fields, you can use it in one or more visualizations.
Tableau Public Dashboards
Step 3: Use the table calculation in the visualization
- From the Data pane, under Measures, drag Running Sum of Profit to Color on the Marks card.
- On the Marks card, click the Mark Type drop-down and select Square.The visualization updates to a highlight table:
![What What](/uploads/1/3/7/9/137933534/378639931.png)
Step 4: Edit the table calculation
- On the Marks card, right-click Running Sum of Profit and select Edit Table Calculation.
- In the Table Calculation dialog box that opens, under Compute Using, select Table (down).The visualization updates to the following:
See Also
This article introduces aggregate functions and their uses in Tableau. It also demonstrates how to create an aggregate calculation using an example.
Why use aggregate functions
Aggregate functions allow you to summarize or change the granularity of your data.
For example, you might want to know exactly how many orders your store had for a particular year. You can use the COUNTD function to summarize the exact number of orders your company had, and then break the visualization down by year.
The calculation might look something like this:
COUNTD(Order ID)
![Tableau Public Price Tableau Public Price](/uploads/1/3/7/9/137933534/416759156.png)
The visualization might look something like this:
Aggregate functions available in Tableau
Aggregations and floating-point arithmetic: The results of some aggregations may not always be exactly as expected. For example, you may find that the Sum function returns a value such as -1.42e-14 for a column of numbers that you know should sum to exactly 0. This happens because the Institute of Electrical and Electronics Engineers (IEEE) 754 floating-point standard requires that numbers be stored in binary format, which means that numbers are sometimes rounded at extremely fine levels of precision. You can eliminate this potential distraction by using the ROUND function (see Number Functions) or by formatting the number to show fewer decimal places.
Function | Syntax | Definition |
ATTR | ATTR(expression) | Returns the value of the expression if it has a single value for all rows. Otherwise returns an asterisk. Null values are ignored. |
AVG | AVG(expression) | Returns the average of all the values in the expression. AVG can be used with numeric fields only. Null values are ignored. |
COLLECT | COLLECT (spatial) A gay-male-oriented discord with both an SFW and NSFW side where you can choose what you see and don't see! A NSFW Discord Server for those who are into monsters, aliens, robots, demons, sca. Nsfw discord icon. | An aggregate calculation that combines the values in the argument field. Null values are ignored. Note: The COLLECT function can only be used with spatial fields. Example: COLLECT ([Geometry]) |
CORR | CORR(expression 1, expression2) | Returns the Pearson correlation coefficient of two expressions. The Pearson correlation measures the linear relationship between two variables. Results range from -1 to +1 inclusive, where 1 denotes an exact positive linear relationship, as when a positive change in one variable implies a positive change of corresponding magnitude in the other, 0 denotes no linear relationship between the variance, and −1 is an exact negative relationship. CORR is available with the following data sources:
For other data sources, consider either extracting the data or using WINDOW_CORR. See Table Calculation Functions. Note: The square of a CORR result is equivalent to the R-Squared value for a linear trend line model. See Trend Line Model Terms. Example: You can use CORR to visualize correlation in a disaggregated scatter plot. The way to do this is to use a table-scoped level of detail expression. For example: {CORR(Sales, Profit)} With a level of detail expression, the correlation is run over all rows. If you used a formula like CORR(Sales, Profit) (without the surrounding brackets to make it a level of detail expression), the view would show the correlation of each individual point in the scatter plot with each other point, which is undefined.See Table-Scoped |
COUNT | COUNT(expression) | Returns the number of items in a group. Null values are not counted. |
COUNTD | COUNTD(expression) | Returns the number of distinct items in a group. Null values are not counted. This function is not available in the following cases: workbooks created before Tableau Desktop 8.2 that use Microsoft Excel or text file data sources, workbooks that use the legacy connection, and workbooks that use Microsoft Access data sources. Extract your data into an extract file to use this function. See Extract Your Data. |
COVAR | COVAR(expression 1, expression2) | Returns the sample covariance of two expressions. Covariance quantifies how two variables change together. A positive covariance indicates that the variables tend to move in the same direction, as when larger values of one variable tend to correspond to larger values of the other variable, on average. Sample covariance uses the number of non-null data points n - 1 to normalize the covariance calculation, rather than n, which is used by the population covariance (available with the COVARP function). Sample covariance is the appropriate choice when the data is a random sample that is being used to estimate the covariance for a larger population. COVAR is available with the following data sources:
For other data sources, consider either extracting the data or using WINDOW_COVAR. See Table Calculation Functions. If expression1 and expression2 are the same—for example, COVAR([profit], [profit])—COVAR returns a value that indicates how widely values are distributed. Note: The value of COVAR(X, X) is equivalent to the value of VAR(X) and also to the value of STDEV(X)^2. Example: The following formula returns the sample covariance of Sales and Profit. COVAR([Sales], [Profit]) |
COVARP | COVARP(expression 1, expression2) | Returns the population covariance of two expressions. Covariance quantifies how two variables change together. A positive covariance indicates that the variables tend to move in the same direction, as when larger values of one variable tend to correspond to larger values of the other variable, on average. Population covariance is sample covariance multiplied by (n-1)/n, where n is the total number of non-null data points. Population covariance is the appropriate choice when there is data available for all items of interest as opposed to when there is only a random subset of items, in which case sample covariance (with the COVAR function) is appropriate. COVARP is available with the following data sources:
For other data sources, consider either extracting the data or using WINDOW_COVARP. See Table Calculation Functions. If expression1 and expression2 are the same—for example, COVARP([profit], [profit])—COVARP returns a value that indicates how widely values are distributed. Note: The value of COVARP(X, X) is equivalent to the value of VARP(X) and also to the value of STDEVP(X)^2. Example: The following formula returns the population covariance of Sales and Profit. COVARP([Sales], [Profit]) |
MAX | MAX(expression) | Returns the maximum of an expression across all records. If the expression is a string value, this function returns the last value where last is defined by alphabetical order. |
MEDIAN | MEDIAN(expression) | Returns the median of an expression across all records. Median can only be used with numeric fields. Null values are ignored. This function is not available for workbooks created before Tableau Desktop 8.2 or that use legacy connections. It is also not available for connections using any of the following data sources:
For other data source types, you can extract your data into an extract file to use this function. See Extract Your Data. |
MIN | MIN(expression) | Returns the minimum of an expression across all records. If the expression is a string value, this function returns the first value where first is defined by alphabetical order. |
PERCENTILE | PERCENTILE(expression, number) | Returns the percentile value from the given expression corresponding to the specified number. The number must be between 0 and 1 (inclusive)—for example, 0.66, and must be a numeric constant. This function is available for the following data sources.
For other data source types, you can extract your data into an extract file to use this function. See Extract Your Data. |
STDEV | STDEV(expression) | Returns the statistical standard deviation of all values in the given expression based on a sample of the population. |
STDEVP | STDEVP(expression) | Returns the statistical standard deviation of all values in the given expression based on a biased population. |
SUM | SUM(expression) | Returns the sum of all values in the expression. SUM can be used with numeric fields only. Null values are ignored. |
VAR | VAR(expression) | Returns the statistical variance of all values in the given expression based on a sample of the population. |
VARP | VARP(expression) | Returns the statistical variance of all values in the given expression on the entire population. |
Tableau Online Sign In
Create an aggregate calculation
Follow along with the steps below to learn how to create an aggregate calculation.
- In Tableau Desktop, connect to the Sample - Superstore saved data source, which comes with Tableau.
- Navigate to a worksheet and select Analysis > Create Calculated Field.
- In the calculation editor that opens, do the following:
- Name the calculated field Margin.
- Enter the following formula:
IIF(SUM([Sales]) !=0, SUM([Profit])/SUM([Sales]), 0)
Note: You can use the function reference to find and add aggregate functions and other functions (like the logical IIF function in this example) to the calculation formula. For more information, see Use the functions reference in the calculation editor. - When finished, click OK.
The new aggregate calculation appears under Measures in the Data pane. Just like your other fields, you can use it in one or more visualizations.Note: Aggregation calculations are always measures.When Margin is placed on a shelf or card in the worksheet, its name is changed to AGG(Margin), which indicates that it is an aggregate calculation and cannot be aggregated any further.
Tableau Cost Model
Rules for aggregate calculations
The rules that apply to aggregate calculations are as follows:
- For any aggregate calculation, you cannot combine an aggregated value and a disaggregated value. For example, SUM(Price)*[Items] is not a valid expression because SUM(Price) is aggregated and Items is not. However, SUM(Price*Items) and SUM(Price)*SUM(Items) are both valid.
- Constant terms in an expression act as aggregated or disaggregated values as appropriate. For example: SUM(Price*7) and SUM(Price)*7 are both valid expressions.
- All of the functions can be evaluated on aggregated values. However, the arguments to any given function must either all be aggregated or all disaggregated. For example: MAX(SUM(Sales),Profit) is not a valid expression because Sales is aggregated and Profit is not. However, MAX(SUM(Sales),SUM(Profit)) is a valid expression.
- The result of an aggregate calculation is always a measure.
- Like predefined aggregations, aggregate calculations are computed correctly for grand totals. Refer to Grand Totals for more information.