Tableau allows users to perform basic arithmetic operations such as addition, subtraction, and division. These operations are essential for many types of data analysis, including financial calculations, performance tracking, and comparative analysis. We’ll explain how to add, subtract, or divide in Tableau, using Calculated Fields.
Table of Contents
Introduction to Calculated Fields
Arithmetic operations in Tableau are performed using calculated fields. These fields allow you to create new data fields based on existing ones, incorporating various mathematical operations. Calculated fields can be created from the .Data Source screen by right-clicking on the name of a column and selecting “Create Calculated Field”
Calculated Fields can also be created from the visual view by right-clicking on a column name and selecting Create, Calculated Field.
Either method will result in a dialog box that allows you to type formulas into it. Formulas can range from simple arithmetic to complex multi-step formulas. We recommend creating new calculated columns from the data source view so you can quickly check your math and make sure that formulas are calculating as expected.
Adding Numbers in Tableau
To add two numbers together in Tableau, first create a new Calculated Field. Then within the formula dialog box use the syntax: [Column1] + [Column2] where the names of your columns reference your dataset.
- Open the Calculated Field Dialog: Right-click on a data column and select “Create Calculated Field”.
- Name the Calculated Field: Give your calculated field a meaningful name, such as “Total Sales”.
- Enter the Addition Formula: Use the addition operator (
+) to add two or more fields.
For example, to add “Sales” and “Tax”, use the formula:
[Sales] + [Tax]Here is an example of two columns being added together in the Tableau interface:
To add more than two columns together, continue to + additional columns in your formula.
Subtracting Numbers in Tableau
To subtract values from each other in Tableau, first create a new Calculated Field. Then use the syntax: [Column1] – [Column2] where the names of the columns being subtracted are referenced with square brackets around them. A new column will be generated with the difference between the values in the two columns.
- Open the Calculated Field Dialog: Right-click in the Data pane and select “Create Calculated Field”.
- Name the Calculated Field: Name your calculated field, such as “Net Sales”.
- Enter the Subtraction Formula: Use the subtraction operator (
-) to subtract one field from another.
For example, to subtract “Discount” from “Sales”, use the formula:
[Sales] - [Discount]Here is an example with a real dataset that calculates a Gross Margin column by subtracting Cost of Sales from Unit Price.
Dividing Numbers in Tableau
To divide two numbers in Tableau, first create a Calculated Field to open up a formula box. Name the Calculated Field and use the syntax: [Column1] / [Column2]. This method works for creating new fields or you can also divide measures that have already been aggregated by referencing the measure names.
- Open the Calculated Field Dialog: Right-click in the Data pane and select “Create Calculated Field”.
- Name the Calculated Field: Name your calculated field, such as “Profit Margin”.
- Enter the Division Formula: Use the division operator (
/) to divide one field by another. For example, to calculate the profit margin as “Profit” divided by “Sales”, use the formula:
[Profit] / [Sales]The following example shows the calculation of a Units field by dividing an Invoice Total by the Unit Sales Price.
Be careful when dividing by zero or null values as it can result in errors and subsequent calculations to fail. If you have these values in your data, either replace them with a value or surround the division calculation with an IF statement to provide an alternative result for times when a calculation is not possible.