Using calculations in Tableau is where you truly start to take your analysis to the next level. Therefore, it is imperative to understand how to use the Tableau Calculated field, or at least familiarize with the main logical functions that can be extremely useful to you.

Like any analytical software, whether that’s Excel, SAS, R, or Tableau, calculations on the Tableau calculated field are the key to going from beginner to advanced.  When you master Tableau calculations and the Tableau calculated field, you master the software tool you are using and you become an infinitely better analyst.  Understanding how these work is crucial to making Tableau work well for you. 

If you are in the field of Finance and Marketing, they would both want to see the Profit Margin applied to various analyses you’ve created.  They would also like some of the charts to provide more instant insight. This is when your knowledge in Tableau Calculated Field will become useful.

How to Create a Calculated Field in Tableau

Here are the simple steps you can follow on how to add calculated field in Tableau and when you want to create a calculation:  

  1. Right click o reate > Tableau Calculated Field, or click the drop down arrow next to Dimensions. 
  1. Name your new Tableau calculation by writing over the box that says Calculation #. 
  1. In the Tableau calculated field window below, write your calculation. 
  1. Click Apply to test the effect of your, and click Ok to apply the changes and to close out of the calculation window. 
Tableau Calculated Field

The next several sections will discuss a variety of Tableau calculation types as well as specifics on how to use Tableau’s built in calculated field functions.  While it won’t go over every Tableau function possible, it will cover the main ones.  To learn about the others, use the menu along the right hand side to see explanatory info on how the functions work as well as an example. 

Aggregate Tableau Calculations & Ratios 

Let’s say you wanted to create a ratio of something in Tableau.  Perhaps it’s Profit Margin (Profit/Sales) or Revenue per Day (Revenue/# Days).  Let’s also say that the lowest level of granularity in your data set is at the customer level.  If you create a ratio calculation in Tableau at the lowest level of grain in your data, it will work appropriately.  If customer A has Sales of $1000, and Profit of $500, it’s a 50% profit.  However, when you aggregate at a higher level – say all customers in a particular region – this Tableau calculated filed calculation will break down.  

Why?  Because Tableau is adding the percentages for each customer in the region and then averaging those percentages.  So, if customer A has a profit margin of 50%, customer B has a margin of 60%, and customer C has a margin of -25%, Tableau will show you a profit margin of 85% (50+60-25).  That’s incorrect.  We need it to sum all the sales and all the profit and then take the ratio of that.  

Example: 

Customer Sales Profit Profit Margin 
Customer A 1000 500 50% 
Customer B 500 300 60% 
Customer C 100 -25 -25% 
Total 1600 775 775/1600= 48% – CORRECT Not (50+60+(-25))/3 =  28% 

To sum all of the profit figures as well as sum all of the sales figures and then divide by the totals, the calculation on Tableau calculated field looks like:  Sum([Profit])/Sum([Sales]).  Tableau now knows to sum the figures first and then calculate the ratio, rather than sum all the individual ratios. 

Calculated Fields Tableau

To create ratios in Tableau, ensure you use the level of aggregation you need in your calculation.  Typically, this will be a sum, but can also be an average, or min/max values. 

Learn more about Tableau aggregation here.

Logical Calculations in Tableau Calculated Field

If/Then 

An If/Then statement in the Tableau Calculated field allows you to check whether certain conditions are true. This formula in the Tableau Calculated Field also gives you the ability to create groups or pull out certain fields from a dimension. They are extremely useful, but using too many with too large of a data set can cause your workbook to slow down.  

IF condition1 THEN x ELSE IF condition2 THEN y ELSE z END 
You can add as many ELSE/IF conditions as you want. Here’s a specific example: 
If [Ship Time] > 3:00pm THEN “Late Afternoon or Evening” 
ELSEIF [Ship Time] > 12:00pm THEN “Mid Afternoon” 
ELSE Morning 
END 

In this case, the first condition will pick up anything that occurs after 3:00pm.  The next condition will pick up anything between noon and 3.  And the last condition will pick up everything else. 

IIF 

IIF is a Tableau Calculated Field formula that is very similar in terms of what it does as the IF statement above, but it’s syntax is more similar to an IF statement in Excel. You can use either statement. 

IIF(test, then, else, [unknown]) 
IIF([Ship Date] - [Order Date] > 5 (days), “Severe Problem”, “OK”) 
Calculated Field Tableau

Case/When 

Case functions are only applicable to string Tableau Calculated fields (i.e. text).  They check whether certain conditions are met within the dimension or parameter and return a value specified by you. 

CASE [Month] 
WHEN “January” THEN “Jan” 
WHEN “February” THEN “Feb” 
WHEN “March” THEN “Mar” 
Etc… 
END 

These can also be used with parameters.  The statement below enables you to select which dimension you want to show in a particular chart.  The setup of the chart remains the same but the information shown can be changed. 

CASE [Choose Dimension Parameter] 
WHEN “Marketing Channel” THEN [Marketing Channel] 
WHEN “Customer Segment” THEN [Customer Segment] 
WHEN “Product Category” THEN [Product Category] 
END 

IfNull 

This Tableau Calculated Field function checks whether a field is null, or has no data in it.  The first Tableau field will be the field you want to use if it isn’t null.  If the Tableau field is null, that’s the second condition.  But, there’s one caveat: the data types have to match between the first and second condition.  If the first Tableau field is a date, the second field must be a date.  If the first Tableau field is text, the second must be text, and so on. 

IfNull([Order ID,0) 

  • This checks whether the product has a ship date.  If it does, it will return the ship date; if it doesn’t have a date, it will return “Hasn’t Shipped Yet.” 

IsNull 

This Tableau calculation function is simply a true/false statement that checks whether a record is null. 

IsNull([Zipcode]) 

  • This checks whether the zip code is null or not.  If you put Number of Records next to it, you can see how many are null vs. how many are populated.  If you use it on the Filter shelf, you can filter out nulls or show only those with nulls.  

You can also combine this with the IF statement for a more advanced filter.   

If  IsNull([Ship Date]) THEN “Hasn’t Shipped Yet” End 

ZN 

The ZN function checks whether measures are null and converts the null values to a zero.  So, imagine the following data: 

Customer ID Amount Paid 
0123 $500 
4567 $2500 
8998      
0009 $3000 

The blank Tableau Calculated field is a null value and will be converted to a zero. 

ZN’s must be aggregated. 

ZN(sum([Amount Paid]) 

Practice Using the Tableau Calculated Field

Since both Finance and Marketing would need to see the Profit Margin applied to various analyses you create and charts to provide more instant insight,  We’ll use Tableau filed calculations to get those. 

  1. Create a Tableau calculated field with the following formula SUM([Profit])/SUM([Sales]). 
  1. Name it Profit Margin. 
  1. Go to the Profit by Marketing Channel worksheet. 
  1. If you don’t have it, bring Marketing Channel to Rows and Profit to Columns. 
  1. Add Profit Margin onto the Tableau calculated field Columns shelf. 
  1. Turn on labels using the Labels shelf. 
  1. Right click on the labels on the profit margin side of the chart and choose Format. 
  1. In the formatting pane at the left, ensure that the Fields drop down menu is set to Agg(Profit Margin). 
  1. Change both the axis and the chart labels to be percentages, 0 decimal points. 
  1. Bring out another instance of Profit in between Profit and Profit Margin on the Columns shelf. 
  1. On this pill you just dragged out, click the drop down arrow and create a Quick Table Calc for Percent of Total.  You’ll now have three charts side by side. 
  1. Sort the Profit Margin in descending order.  Determine some areas where you could have some quick wins, like Social Media. 
  1. Rename the Tableau calculated filed tab “Profit Margin by Marketing Channel.” 
  1. Create a new Tableau calculated field.  This will combine both a date calculation and a case statement.  Type in the following calculation: 
CASE Datename('month',[Order Date]) 
WHEN 'January' THEN 'Winter' 
WHEN 'February' THEN 'Winter' 
WHEN 'March' THEN 'Spring' 
WHEN 'April' THEN 'Spring' 
WHEN 'May' THEN 'Spring' 
WHEN 'June' THEN 'Summer' 
WHEN 'July' THEN 'Summer' 
WHEN 'August' THEN 'Summer' 
WHEN 'September' THEN 'Fall' 
WHEN 'October' THEN 'Fall' 
WHEN 'November' THEN 'Fall' 
WHEN 'December' THEN 'Winter' 
END 
  1. Name the calc “Seasons.”  This is saying that if the text version of the month equals the following names then classify them according to their appropriate season. 
  1. Now find the Order Qty by Day chart we created.  It probably hasn’t yet been used in a dashboard so you’ll need to go to the worksheet directly. 
    • If you don’t have it, bring Order Quantity to Rows.  
    • Bring Order Date to Columns
    • Click the drop down arrow on Order Date and set it to be a continuous Month (May 2015) from the second section of dates in the menu. 
  1. Now, change the Order Date pill on Tableau Calculated field from a continuous (green) day to a discrete (blue) Week Number, accessed via the sideways arrow in the first date section of the drop down, where it says “More.” 
  1. Drag your Seasons calc onto Color. 
    • If you can’t find where your Seasons calc went, use the magnifying glasss up next to Dimensions. 

We hope you learned a lot about creating Tableau Calculated Field and the most useful functions you can use in your data analysis. We will also discuss the different Tableau Date Functions. To learn Tableau faster, be sure to check out our Tableau Classes for individuals and corporations.