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 2nd 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 2nd 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 2nd 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
