# Tableau Table Calculations

In Tableau, a user can perform a different type of computations using table calculations. For example, using table calculations user can calculate the running total of sales or the percent of total profit across countries etc.

**Summary/ Objective:**

In this article, I am going to brief about different table calculation functions and their uses in Tableau.

## FIRST

** FIRST() :** It returns the number of rows from the current row according to the partition dimensions specified by the user

__Example:__ The below snapshot shows the yearly population across regions. When * FIRST()* is being calculated with respect to “Year” and “Region” the offset of the first row from the second row is -1

## INDEX

** INDEX() : **INDEX function, returns the row number according to the partition dimensions specified by the user

__Example:__ When * INDEX()* is being calculated across “Year” and “Region”, the values returned are 1, 2, 3, … etc.

## LAST

** LAST() : **It returns the number of rows from the current row to the last row according to the partition dimensions specified by the user

__Example:__ The below snapshot shows the yearly population across regions. When * LAST()* is being calculated with respect to “Year” and “Region” the offset of last row from second row is 16

## LOOKUP

** LOOKUP() : **LOOKUP() is an important table calculation function. It returns the value of the specified expression in a “target row”.

“target row” is specified as a relative position of the row from the current row. FIRST() + n or LAST() – n, can be used as to specify the target row relative to first/last rows in the partition dimensions.

__Expression__: LOOKUP(expression , [offset])

__Example:__ The below snapshot shows the yearly population across regions. When * LOOKUP(AVG[Population],2])* is being calculated with respect to “Year” and “Region”, each row shows the value of the next two year and region

## RANK

** RANK() : **It returns the standard rank of the dimension corresponding to the given measure. If there are identical values, then same rank is assigned with gaps inserted into the number sequence

__Expression__: RANK(expression , [‘asc’|’dsc’])

‘asc’ : This argument is used to rank the values in ascending order

‘dsc’ : This argument is used to rank the values in descending order

By default, RANK function takes descending order

__Example:__ For example, let say there are Regions = {A, B, C, D, E} and respective Sales in Regions = {$400, $200, $400, $250, $400}

Now, if we calculate RANK() across Regions the output value would be {1,5,1,4,1}.

The same value is being assigned same rank and gaps are inserted into the number as next rank after 1 is 4.

** RANK_DENSE() : **It returns the dense rank of the dimension corresponding to the given measure. If there are identical values, then same rank is assigned but no gaps are inserted into the number sequence

__Expression__: RANK_DENSE(expression, [‘asc’|’dsc’])

‘asc’ : This argument is used to rank the values in ascending order

‘dsc’ : This argument is used to rank the values in descending order

By default, RANK function takes descending order

__Example:__ For example, let say there are Regions = {A, B, C, D, E} and respective Sales in Regions = {$400, $200, $400, $250, $400}

Now, if we calculate RANK_DENSE() across Regions the output value would be {1,3,1,2,1}.

The same value is being assigned the same rank and no gaps are inserted into a number as next rank after 1 is 2.

** RANK_UNIQUE() : **It returns the unique rank of the dimension corresponding to the given measure. Identical values are assigned different ranks.

__Expression__: RANK_UNIQUE(expression, [‘asc’|’dsc’])

‘asc’ : This argument is used to rank the values in ascending order

‘dsc’ : This argument is used to rank the values in descending order

By default, the RANK function takes descending order

__Example:__ For example, let say there are Regions = {A, B, C, D, E} and respective Sales in Regions = {$400, $200, $400, $250, $400}

Now, if we calculate RANK_UNIQUE() across Regions the output value would be {1,5,2,4,3}.

## RUNNING SUM

** RUNNING_SUM(): **As the name suggests, it returns the running sum value from the first row to the current row according to partition dimensions specified by the user

__Example:__ The below snapshot shows the yearly population across regions. When * RUNNING_SUM(AVG[Population])* is being calculated with respect to “Year” and “Region”, it returns the aggregated value of from first row to current row

Above, the value in 2^{nd} row of * RUNNING_SUM(AVG[Population]) *is addition of first two rows value i.e. 18,462,813 + 110,504,288 = 128,967,101

## RUNNING AVERAGE

** RUNNING_AVG(): **It returns the running average value from the first row to the current row according to partition dimensions specified by the user

__Example:__ When * RUNNING_AVG(AVG[Population])* is being calculated with respect to “Year” and “Region”, it returns the aggregated average value of from the first row to current row

Above, the value in 2^{nd} row of * RUNNING_AVG(AVG[Population]) *is average of first two rows value i.e. (18,462,813 + 110,504,288)/2 = 64,483,550

## RUNNING MIN & MAX

** RUNNING_MIN/MAX(): **It returns the running minimum/maximum value from the first row to the current row according to partition dimensions specified by the user

__Example:__ When * RUNNING_MIN(AVG[Population])* is being calculated with respect to “Year” and “Region”, it returns the minimum value of from first row to current row

Above, the value in 2^{nd} row of * RUNNING_MIN(AVG[Population]) *is minimum of first two rows value i.e. MIN(18462813 , 110504288) = 18462813

## SIZE

** SIZE() : **It returns the number of rows within partition dimensions specified by the user

__Example:__ The below snapshot shows the yearly population across regions. When * SIZE()* is being calculated with respect to only “Year”, it returns the total number of “Year” value present in the view

## TOTAL

** TOTAL() : **It returns the total of the given expression across the partition dimensions specified by the user

__Example:__ When * TOTAL(SUM[Population])* is being calculated with respect to “Year” and “Region”, it returns the total sum of all “Population” values in each row