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.
Table of Contents
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. There are three main types of calculations Tableau provides in Tableau calculated fields:
- 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.
- 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.
- 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.
Hevo offers a faster way to move data from databases or SaaS applications like Tableau, Salesforce, Google Ads, Zendesk & 100+ Sources (40+ free source connectors) into your Data Warehouses like Redshift, Google BigQuery, Snowflake and Firebolt to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.
Get Started with Hevo for Free
Check out some of the cool features of Hevo:
Sign up here for a 14-Day Free Trial!
- Completely Automated: The Hevo Platform can be set up in just a few minutes and requires minimal maintenance.
- Real-time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
- 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
- Scalable Infrastructure: Hevo has in-built integrations for 100+ sources with 40+ free source connectors that can help you scale your data infrastructure as required.
- Tremendous Connector Availability: Hevo houses a diverse set of connectors that authorize you to bring data in from multiple data sources such as Google Analytics, HubSpot, Asana, Trello, Amplitude, Jira, and Oracle, and even Data-Warehouses such as Redshift and Snowflake in an integrated and analysis-ready format.
- 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support calls.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
- Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
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, like shown in the animation below:
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 as shown below.
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 as shown below.
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:
|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. |
|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. |
|ZN||ZN(expression)||The ZN function returns the provided expression if not found NULL, otherwise zero.|
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:
- 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.
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
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. To pique your interest we have a few more blogs to help better your use with Tableau:
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 100+ 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.
Visit our Website to Explore Hevo
Hevo lets you migrate your data from your database, SaaS Apps to any Data Warehouse of your choice like Amazon Redshift, Snowflake, Google BigQuery, or Firebolt within minutes with just a few clicks.
Why not try Hevo and see the magic for yourself?Sign Up here for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also check out our unbeatable pricing and make a decision on your best-suited plan.
If you have any questions on Tableau calculated fields, do let us know in the comment section below. Also, share any other topics pertaining to the Tableau business application you’d want us to cover. We’d be happy to know your opinions.