The Tableau TOTAL function is one of the Table Calculation Functions, which will return the total for the given expression or column in a table based on the Compute using the option in the Table calculation partition. It is one of the most popular Table Calculations applied on top of the aggregate function.
This article shows how to use this Tableau TOTAL Table Calculation function to find the total at the level of details (lods) and what the difference is between SUM() and TOTAL() with examples.
The syntax of the Tableau TOTAL Function is as shown below.
TOTAL(expression)
What is the difference between Tableau SUM() and TOTAL() functions?
The SUM() function is an aggregate function like AVG(), MIN(), etc, which will return the sum of all values in a column or expression. At the same time, the Tableau TOTAL() function is a Table calculation with more options to compute the total of a given expression (it may be SUM). You can calculate the total for the whole table, partition, or a particular sub-section or Dimension. If you choose the compute option as Pane(across), the SUM() and TOTAL() functions return the same result.
Tableau TOTAL function Examples
To demonstrate this Tableau TOTAL Table Calculation function, Drag and drop the Sample Superstore Excel Sheet Order Date into the Rows shelf. By default, it selects the Year, so please click the Plus button to expand the Order Date to Year and Quarter. Next, add the Sales to Text Shelf to show the Measure Values.
First, we must create a calculated Field for writing an expression of the Tableau TOTAL() function. So, go to the Analysis Menu and select the Create Calculated Field.. option.
In this Tableau example, we want to use the TOTAL() function on SUM(Sales), so please write the below expression.
TOTAL(SUM([Sales]))
Once you write the above expression, a blue URL (Default Table Calculation) will appear at the bottom. Please refer to the Table Calculations, Aggregate Functions, and remaining Functions articles in Tableau.
Click that blue hyperlink to open the window Table Calculation [Total]. You can change the Compute Using from Automatic to field, table, etc. The Compute Using option’s drop-down list lists all the available fields in the Sample Superstore.
If you select the Advanced Option from the drop-down menu, a new window will allow you to choose the Partitioning fields and sort options. You can change the Restarting point and the level of detail for the Tableau TOTAL function. However, we don’t use this window for all this, so let me close it.
Observing the Calculated Field shows a message: Totals summarize values from Table(across). It chooses the Table(across) by default, but we can change it later.
Next, double-click the Total Measure to add the newly created calculated field to the table. If you observe the table, all rows are filled with 2,326,534 because it is the sum of all rows in a table.
Once the Table calculation happens, Tableau will perform the same TOTAL()function calculation for the whole table or the particular category. For instance, if we add Segment to the Columns Shelf, the TOTAL()function calculates total sales for Consumers, Segment Corporate, and Home Office. Here, 1,170,660 means Total Consumer Sales starting from 21965, 48943,… to 123601.
Within the Measure Values shelf, click the Down arrow beside the Total Calculated field and select the Edit Table Calculation option from the context menu. It will open the Table Calculation window below to change the Compute Using option.
Let me change to Table(across). It will display the Total Sales for each quarter for all the segments. Q1 = 21, 965 + 14, 273 + 39, 734 = 75, 972.
If you choose the Pane(across), the Tableau Total table calculation and the aggregate SUM() function will return the same result.
If you select the Compute Using option as the Specific Dimension and the Dimension is the Year of Order date. It calculates the total for each Q1 in each year and so on.
Q1 of 2019, 2020, 2021, and 2022 = 21,965 + 40,963 + 46,744 + 64,456 = 174,128.
