Table Calculations & Calculated Fields in Tableau

Table calculations are a special type of calculation that is done on the visible data in the worksheet. There are inbuilt table calculations that are already integrated into the tableau app, such as percentage of the total, moving average, running total, rank, etc.,

Calculated Fields are created on a data level and appear as a separate column in the data source. They stay in the Data pane and be re-used in different analysis easily.

Table Calculations are simpler and their scope is more limited compared to Calculated fields. Calculated fields are much more diverse enabling deeper analysis.

Let’s learn how to create table calculations in Tableau with an example.

We are using Superstore Dataset for this example

• Percent Of Total

Step 1: Create a text table for sales for each subcategory. Add< Sub-category > to rows and <Sales> to Label under Mark.

Step 2: To know the percentage distribution of the values of the sub-category, click on the SUM(sales) under Marks and select Quick Table calculation and select Percent of Total.

Step 3: Now all the values will be converted to respective percentage values to the total sales of the Sub-Category. And the drop-down next to Sum(Sales) will be changed to Δ icon which indicates a table- calculation has been applied.

• Rank

Next, let’s find the State which has the highest Sales using Quick Table Calculation

Step 1: Now add State to rows and SUM(Sales) to Text under Marks.

Step 2: Click on the drop-down in SUM(Sales) and select Quick Table Calculations and select Rank.

Step 3: Now all the values corresponding to the state will be changed to Rank based on the Total Sales. We can sort them in ascending order and view the state which is highest and 2nd or 3rd..

So the first State with the highest Sales is California and we can rank each state accordingly.

• Running Total

Next, Let's learn how to use the Running Total Calculations in Tableau.

Step 1: Add Order Date< YEAR (Order Date> to rows and SUM(Sales) to Text under Mark.

To find out the Running Total of Sales every year click on SUM(Sales) under Marks and select Quick Table Calculation and Running Total.

Now drag the <Sales> and add to the table to show the individual Sales for each year.

Calculated Fields are created on a data level and appear as a separate column in the data source. They stay in the Data pane and be re-used in different a easily.

These are some of the in-built Table Calculations in Tableau which are commonly used for Visualization.

Calculated Fields

Calculated fields are calculations created by the user using different functions and fields available in the data source. This will help to derive more insight from visualization based on the requirement. It is also an alternative for groups, sets and filters.

Another key advantage of creating calculation fields is to transform data at the row level based on one or more variables. Changing the unit of a variable is a common example. You may wish to standardize the value of international sales in US dollars or convert imperial units to metric.

While those row-wise calculations will return a value for every row of the dataset, you can also calculate aggregated measures, also known as summary statistics, to return a single value such as average, variance, minimum, and maximum.

Let's create a simple calculated field in tableau.

We need to make an analysis whether the product is profit /loss.

Step 1- Add Sub-Category to rows and SUM(Profit) to label under marks. Sort them in descending order.

Step 2: Select the Calculated field in the Data pane.

Step 3 :Now rename the field as Profit/loss. And type the below calculation.

IF sum([Profit]) > 0 THEN "Profit"

ELSE "Loss" END

Step 4: Click Apply and click OK. Now a new Calculated field <Profit/Los> will be added in the DATA Pane. Add this new calculated field in rows. We get a separate row that indicates which Sub-Category is in Loss and which one is in Profit.

This is how we create calculated fields in Tableau.

Tableau Calculated Field Order Of Operations

Now that we know about IF statements, let’s learn a bit more about some complex logical issues we may encounter.

When we have an expression consisting of multiple logical expressions, the order of precedence plays an important role. The order of operations is just like the one used in equations in mathematics. The precedence order of the operations is as follows:

• Parentheses

• NOT

• AND

• OR

Hence, the first thing to be computed in a logical statement is the parentheses, which group comparisons together. The next one is NOT, followed by AND then OR. It is always recommended to use parentheses to group your conditions.

Level Of Detail

Level of Details expressions(also known as LOD expression allows you to compute values at the data source level and the visualization level. Level of Details can be added to dimensions or measures.

There are three types of LOD in Tableau.

• Fixed LOD

• Include LOD

• Exclude LOD

Fixed LOD calculates the expression based on a specific dimension without any relation to the dimension present or included in the visual. It can be considered as base value calculation. All LOD calculation starts with a { bracket.

Include LOD computes values using the specific dimensions in addition to whatever dimensions are in the view. Include LOD provides a more granular level of values.

Exclude LOD deducts or subtracts the dimension present in the visual or worksheet and computes the calculation. It would display values higher than the values present in the visual.

In conclusion, Table Calculations are simpler and their scope is more limited compared to Calculated fields. Calculated fields are much more diverse enabling deeper analysis.