Tableau Calculated Fields: Easy Steps, Key Types & Best Practices 101

Tableau allows you to create new fields in your workspace if your underlying data sources don’t have them. These new fields are termed Tableau calculated fields and they can be used to examine data, experiment with ratios, create data comparisons, and discover new possibilities.

If you work as a data analyst, finance, or marketing professional, you probably deal with loads of data that has to be processed once it has been submitted to your Business Intelligence platform. In Tableau, this is a no-sweat process because the premise of utilizing Tableau calculated fields is to do new calculations on the fly. Tableau calculated fields are quite useful when you work on data visualizations to deliver fast insights.

This blog will help you learn about Tableau calculated fields. We’ll go through steps to create them, steps to edit them, logical functions and their use, and finally some tips and best practices for using Tableau calculated fields. 

What is Tableau?

Data-driven decision-making demands insights into what’s happening around. Sure, every company gets bombarded with tons of data, and unless they really know how to put it to use, every attempt to solve a problem is a wild goose chase.

Tableau makes decision-making easier by visualizing your data in seconds. A market leader in the Data Analytics and Business Intelligence industry, Tableau has garnered the likes of Nike, Coca-Cola, Skype, The World Bank, The New York Times, and many more.

Tableau can readily connect to your data source/files- be it excel sheets, database applications, cloud-based data warehouses, or big data storage. Whether you are an analyst, data scientist, student, teacher, executive, or business user, Tableau’s fast-to-deploy and an easy-to-learn interface can create highly simplified graphs or charts for any set of complex data. 

Tableau’s user-first philosophy takes this application far ahead of the pack. Gartner’s Magic Quadrant has lauded Tableau as a leader in Analytics & BI Platforms, for nine consecutive years. With thriving possibilities in the data analytics space, and its strong collaboration with its parent, Salesforce, Tableau is on a road to a shining future. 

Business Benefits of Using Tableau

• One Source of Truth: Separate data silos with different BI applications are a thing of the past. Using Tableau you can bring in data from multiple sources like Excel, SQL databases, CRMs like Salesforce without having to write any code.
• Instant & Automated Reporting: Tableau is an interactive data visualization software that helps you build informative and eye-catching reports quickly. These include pie charts, bar charts, bullet charts, gantt charts, boxplots, and a ton more, which can be automated to bring in new data visualizations.
• Advanced Dashboards: Tableau dashboards provide an in-depth view of your data using advanced visualizations. With Tableau dashboards, you get to visualize data in multiple views and objects, and in a variety of layouts and formats to choose from.
• Natural Language Processing: Using Tableau’s natural language processing, you can simply type in “What is the average revenue generated last quarter?”, and Tableau will return you detailed answers in an instant. 
• Robust Security: Tableau uses strict measures to ensure your users’ data and business data are secured. It houses a security system based on permission and authentication mechanisms for user access and data connections.
• Tableau Community: You can gain immense benefits when you join the Tableau Community. Being a member, you can upskill, build powerful connections, get inspired by the community, and offer mentorship to newcomers.

For further information on Tableau Dashboards, read more here- What is Tableau Dashboard and How to Build it?. To know more about building reports in Tableau, have a look at Building Tableau Reports: A Comprehensive Guide.

What are Tableau Calculated Fields?

As the name suggests, these are calculated fields. If your spreadsheet application contains columns for revenue and cost, you can use Tableau calculated fields to calculate net profit margin ((revenue – cost) / revenue), after uploading your spreadsheet file to your Tableau workspace.

Tableau calculated fields create and add new dimensions to your files- like ratios, functions, aggregations, logical operations.

Types of Calculations

There are three main types of calculations Tableau provides in Tableau calculated fields:

1. Basic expressions: Basic operations transform your data at row level or at the visualization level of detail (an aggregate calculation). An example can be a split function to split first name and last name, or a sum function to add your monthly expenses.
2. Level of Detail (LOD) expressions: Level of detail expressions give you more granular control and information about the values at the data source level and visualization level. Examples include finding purchase repetitions starting 20xx  from a FIXED customer/brand. 
3. Table calculations: Table calculations apply to values in a visualization. It transforms local data present in your tables without considering any measures or dimensions that are filtered out of the visualization. Examples include calculating percent change from a reference date or a common baseline. 

For more information on Tableau calculated fields’ calculations, visit their official support page here- Types of Calculations in Tableau. If you would like to learn more about Tableau table calculations, do read our helpful guide here– Tableau Table Calculations 101: Functions & Examples Simplified.

Why You Should Use Tableau Calculated Fields

You might wonder, why not solve computations and create new fields beforehand? Well, there are a plethora of reasons to consider:

• It’s not every time that to-be-derived parameters will pop into your mind. For the most part, needed calculations will come after you have uploaded your data, and Tableau calculated fields are a time savior in such situations. You get to segment and calculate new parameters on the fly.
• In some cases, you may require to prove/test a concept, which you would not want to inscribe in your standard spreadsheet document. Tableau calculated fields allow you to experiment with data, and check for new possibilities.
• Sometimes data comes with unwanted elements- filters, or columns that are not required. Using Tableau calculated fields, you can filter out the unnecessary columns and visualize only what matters to you.
• Tableau calculated fields also allow you to take advantage of their built-in functions, which can be readily used on your data with a few simple clicks. Doing so prior would mean writing functions, moreover error-free in your spreadsheet application which would prove cumbersome.

Steps to Create a Simple Calculated Field

Having been briefed on the Tableau Business Intelligence application and Tableau calculated fields, let’s start off with steps to create a simple Tableau calculated field. For instance, you can, in Tableau create calculated field called gross profit margin, which is (Total Sales – Total Costs) / Total Sales. This translates to a Tableau formula like this:

(SUM([Sales]) - SUM([Costs])) / SUM([Sales])

Our above formula uses the SUM function to find the gross profit margin. To calculate and enable this field for your dataset, follow the steps below:

Step 1: Create Tableau Calculated Field

Go to your Tableau worksheet and visit Analysis > Create Calculated Field. A dialog box will open up.

Step 2: Enter Your Formula 

Here, provide a name to your function, eg. Gross Profit Margin, and enter the formula as described above. As an alternative, you can also drag and drop measures like Sales and Costs from the left pane and create your gross profit margin formula.

If you prefer working with some other formula, which you don’t know or have forgotten, you can refer to the available functions in Tableau by clicking on the side arrow. This will open up all the functions available in Tableau. 

From this screen, you can select available functions from the list, use them in your formula box and view their syntax and other information from the right pane. 

When you have entered your formula, select OK. 

Your new field, Gross Profit Margin will now be available in the Tableau Data Pane. If the new field computes quantitative data, it gets added to Measures. If it computes qualitative data, it gets added to Dimensions. 

Note: All calculated fields have equal signs (=) next to them in the Data pane.

You can visit your data source, and see the computations for the newly created Tableau field in a new column.

Steps to Use Tableau Calculated Fields in the View

To view your newly created Tableau calculated fields in the sheet, follow these steps:

Step 1: Build Your View

The first step in viewing your Tableau calculated fields is to build your rows and columns. From the Dimensions pane, select your desired fields to be filled as rows and columns and drag them to their corresponding boxes. Add subcategories to segregate your columns/rows.

Step 2: Add the Calculated Field to the View

The next step is to add your Tableau calculated fields in the sheet view. From the Measures/Dimensions tab, drag your newly created Tableau field to the Color tab on the Marks card. Your field will automatically be aggregated.

To change this representation from SUM(Discount Ratio) to AVERAGE(Discount Ratio), right-click on SUM(Discount Ratio) and select Measure (Sum) > Average. You can also select more options like percent difference, running total, moving average, and many more from Quick Table Calculation. 

How to Edit Tableau Calculated Fields

At times your Tableau calculated fields might require a change in the formula for better and more relevant data visualization. Tableau provides you the ability to modify your current calculated fields which gets updated across your entire workbook. To edit your existing Tableau calculated fields, follow the steps below:

Step 1: Right Click Your Tableau Calculated Field

Select your calculated field(s) and right-click on them to open the Edit option.

Step 2: Modify Your Field

In the Calculation editor, you can set a new name for your field or update the formula. To do so, you simply have to select the text and replace it with the desired one. 

Hit OK, when you are finished with your new changes.

As soon as your Tableau calculated fields change, the view also updates your data using the new formulation automatically. You do not need to re-add the updated calculated field to the view. 

Aggregate Tableau Calculations & Ratios

Tableau workspace automatically aggregates your data for every instance when a Measure is brought out into the view. Let’s understand how this affects your decisions and data through an example:

Suppose you own four retail shops in the US. You need to find out the gross profit margin from each shop, and hence you define a Tableau calculated field for gross profit margin as:

[Sales] - [Costs] / [Sales]

This formula works fine as long as you are checking gross profit margins for each store. Notice that in our formula, we didn’t specify the SUM function, as done previously. This means that once you compile your store sales and costs in a table, the aggregate looks like this:

Store           Sales ($)	Costs ($) 	Gross Profit Margin (%)
Store 1 	10,000	        4,000		60%
Store 2	        25,000	       13,000	        48%
Store 3	        46,000         27,000	        41%
Store 4	        25,000	        8,500		66%
Total 	       106,000	       52,500	        53.75%

Notice the last entry in your gross profit margin column. The figure comes out incorrect as 53.75% which is nothing but the average of all gross profit margins of individual stores. The correct figure is 50.47% which is 3.27% less than the calculated one.

So, why is there this anomaly?

Tableau understands what’s been said to it. So, it does find gross profit margin accurately when told for individual stores. But when it comes to finding the aggregate, Tableau simply finds the average and displays the figure. 

When you work with such datasets, you need to rethink the level of granularity being set while writing your Tableau calculated fields or ratios. If you instead write your gross profit margin as:

(SUM([Sales]) - SUM([Costs])) / SUM([Sales])

Tableau will know exactly what needs to be done, and hence correct results will be displayed. It will sum the figures first and then use them in the formula rather than summing all the individual ratios. Instead of SUM, you can also use other operators like AVERAGE, MAX, MIN, and many more.

Logical Calculations In Tableau Calculated Field

This section introduces you to logical functions available in Tableau that you can use while framing your own Tableau calculated fields. Logical calculations allow you to determine if a certain condition is true or false (boolean logic) and produce results accordingly. 

Here are six main logical functions that are commonly used:

Function	Syntax	Description
CASE	CASE <expression> WHEN <value1> THEN <return1> WHEN <value2> THEN <return2> … ELSE <default return> END	CASE function evaluates the expression and returns the result according to the values obtained.  This function is easier to use compared to IF and IF THEN ELSE. Eg- CASE [Store] WHEN ‘Store1’ THEN 1 WHEN ‘Store2’ THEN 2 ELSE 3 END
IF	IF <expr1> THEN <then> [ELSEIF <expr2> THEN <then2>…] [ELSE <else>] END	The IF logical function tests a series of expressions and returns <then> according to the condition that gets true. Eg- IF [Profit] > 0 THEN ‘Profitable’ ELSEIF [Profit] = 0 THEN ‘Breakeven’ ELSE ‘Loss’ END
IFNULL	IFNULL(expr1, expr2)	The IFNULL function evaluates two expressions expr1 and expr2 and returns expr1 if it’s not null, otherwise expr2.  Eg- IFNULL([Gross Profit Margin], 0)
IIF	IIF(test, then, else, [unknown])	The IIF function checks your test expression and returns one value if TRUE, another value if FALSE, and an optional third value or NULL if unknown. Eg- IIF([Profit] > 0, ‘Profit’, ‘Loss’)
ISDATE	ISDATE(string)	The ISDATE logical function returns true if a given string is a valid date. Eg- ISDATE(“2020-07-07”) = True
MAX	MAX(expression) or Max(expr1, expr2)	MAX function returns the maximum value across all records or a maximum of two expressions for each record.  Eg- MAX([Profit])
MIN	MIN(expression) or MIN(expr1, expr2)	MIN function returns the minimum value across all records or a minimum of two expressions for each record.  Eg- MIN([Profit])
ZN	ZN(expression)	The ZN function returns the provided expression if not found NULL, otherwise zero. Eg- ZN([Profit])

Tips and Best Practices on How to Create Calculations and Calculated Fields

Lastly, we sum up our blog with some of the tricks and best practices you can use while creating your own Tableau calculated fields:

Tips

• Use drag and drop fields: Tableau features a drag and drop mechanism to transfer your existing Measures from the left pane into your Tableau calculated fields formula section. What this means is that, you don’t have to worry about your previously named conventions, you can simply search and select your measures, and drop them to your calculation editor.
• Open function reference for quick entry: Function reference is a list that appears on the right side in the calculation editor. This helps you to use the function and type the code expression correctly according to its syntax.
• Take advantage of autocomplete: When you are typing functions, you can use Tableau’s autocomplete feature that suggests you options to complete items in your formula. To use this feature, simply press enter when typing the function.
• Resize test in the calculation editor: Use Ctrl and + to zoom into your calculation editor (Command and + in macOS). The magnification lasts until you close the editor.
• See sheets affected: When you edit Tableau calculated fields in the calculation editor, you can click and see which other sheets are using the field from the Sheets Affected tab beside the Apply button.

Best Practices

Although you can in Tableau create calculated field, and sort your calculations, there’s a forethought to consider. Whenever you are working with Tableau calculated fields, it’s important to know your data types while writing functions.

Calculated fields with data types like Boolean or integers are processed rapidly. When you use strings in your calculated fields, they run slowly and have an influence on the performance of your worksheet, especially if you have huge datasets.

To prioritize performance, here are some factors you should consider:

• Convert IF/THEN statements to a Boolean. 

As an example, you can convert 

IF [Region]="America" THEN "Match" ELSE "No Match" END

to

[Region]="America"

and use aliases to change True to Match and False to No Match.

• Convert dates to integers to allow for simple addition and subtraction, instead of DATEDIFF() or DATEADD.

• In parameter-based computations, use an integer as the parameter value and a string value as the alias to reduce the requirement for slower string comparisons.

This brings us to the end of this blog.

Also, take a look at Advanced Tableau Analytics Made Easy to get a more detailed understanding of your Tableau data.

Conclusion

To recap, this blog presented information about Tableau calculated fields that you can use to add new fields or experiment with new data. Like any other essential data analysis software, whether it’s Excel, SAS, R, or Tableau, learning how to use calculations to custom craft your business performance indicators is a key to a better understanding of your data. When you can, in Tableau create calculated fields, you become a better analyst and create powerful data visualizations.

While using Tableau you might face hurdles in bringing data from multiple sources- you can have SQL databases like MySQL, PostgreSQL, NoSQL databases like MongoDB, SaaS applications like Zendesk, Intercom, CRMs like HubSpot, Salesforce, and whatnot. Integrating data from such a mammoth of sources without using code is difficult. But with Hevo, it’s a cakewalk.

Hevo Data with its strong integration with 150+ Sources & BI tools such as SaaS Applications, CRMs, Databases allows you to not only export data from sources & load data in the destinations, but also transform & enrich your data, & make it analysis-ready so that you can focus only on your key business needs and perform insightful analysis using BI tools.

Sign up for a 14-day free trial and simplify your data integration process. Check out the pricing details to understand which plan fulfills all your business needs.

Frequently Asked Questions