This is the second part of a series of posts, see part 1 here.

Calculated fields in Tableau very similar to Excel formula, so if you have a reasonable understand of how Excel formula are used, transitioning this knowledge to Tableau can be straightforward.

There are three major families of calculated fields in Tableau, and each has an equivalent in Excel – only the first two are dealt with in this post.

1.  Non-aggregate calculations – calculations which are meaningful on each row of the underlying data

2.  Aggregate calculations – calculations which are ONLY meaningful after the data has been aggregated

3.  Table calculations – these will be dealt with in a later post

1.  Non aggregate calculations.

 

These are the simplest type of calculation, and without doubt the most commonly used. 

Non-aggregate calculations are performed for each row in the underlying data, rather than being performed on aggregated data (such as you would find in a pivot table or Tableau view).

 

In Excel

In Excel, non-aggregate calculations are created in the worksheet which contains the raw data – typically be creating a new column and adding a formula such as:

 

Example 1:  Calculating profit by subtracting cost amount from sales amount

=(A2-B2) where column A contains the sales amount and column B the cost.  The resulting column would be titled ‘Profit’.

 

Example 2:  Determining the first letter of a product name

=LEFT(C2,1) where column C contains the product name

 

In Tableau

 

Calculated fields are added in much the same way in Tableau.  Get started by right clicking in the data window, or by using the pull down menu at the top of the data window shown in the image.

 

 

 

In Tableau calculated fields are added in the same way, regardless of whether you are adding a non-aggregate or aggregate calculation – but it is easy to tell which you are creating!  If you’re not using aggregate functions, then you’re creating a non-aggregate field.

 

A non-aggregate calculation in Tableau is defined as a calculated field which does not use any functions from the ‘Aggregation’ function group.

 

 

For example – [Sales] – [Cost] would be a non-aggregated calculation.  The result of this calculation would be provided for every row in the underlying data.

In summary, a non-aggregate calculation will appear roughly as below in either Tableau and Excel:

in Excel                   =(A2-B2)               Making reference to the data by column ID

in Tableau           [Sales] – [Cost]          Making direct reference to the fields by name

 

To help with your understanding of non-aggregate calculations, it is useful to know that these type of calculations can be created EITHER in your underlying data (say Excel or SQL Server) OR in Tableau.  Exactly where you should create them depends on your specific circumstances…

If your data source is shared by many users…  consider creating calculations in the underlying data so your team can all benefit from them

If your data source changes regularly…  suppose you receive a new excel file every day for example, consider adding your calculations to Tableau so you don’t have to recreate them in the underlying data

 

 

2.  Aggregate calculations

 

Aggregate calculations are those that use aggregate functions.  Examples of aggregate functions are SUM, AVG, MAX & MIN (there are a few others).

Therefore an example of an aggregate calculation would be:

 

Profit Ratio = SUM(Profit) / SUM(Sales)

 

But what is special about aggregate calculations?

 

The primary difference between aggregate and non-aggregate calculations is that aggregate calculations often can’t be sensibly calculated for each row in the underlying data set – it normally only makes sense to calculate them when the data is aggregated.

Consider the profit ratio example, based on the following data:

 

 

The final column – profit ratio (NA) – is a non-aggregate calculation.  This has been calculated on each row in the underlying data.  BUT – this is not very useful in this situation, sure it tells us the profit ratio for each sales transaction, but it can’t be used to summarise the profit ratio for our organisation.

If you average this value, you’ll get a misleading figure of 35% for the organisation profit ratio, when the organisation profit ratio is actually very close to 50%.

To correctly calculate the organisation profit ratio, we need to make use of aggregate functions, thus creating aggregate calculations.

Using the calculation SUM(Profit) / SUM(Sales) forces the sales and profit amounts to be totalled BEFORE the division occurs, and as the table below now shows, provides the correct answer for profit ratio of 50% (actually a tiny bit less, the answer has been rounded).

 

Order ID	Sales	Profit	Profit Ratio (NA)
1	£100	£20	20%
2	£1,000,000	£500,000	50%
TOTAL	£1,000,100	£500,020	50%

 

 

In Excel

 

If you’re using Excel, these type of calculations are likely to require a pivot table.  You would select your data set and create a pivot table.  Once this is done, you can add a calculated field which provides the profit ratio.  Follow these steps to replicate this using Tableau’s superstore data, but using Excel.

STEP 1.  Locate superstore sales – it’s within “My Documents | My Tableau Repository | Data Sources”

 

STEP 2.  Select the data from the orders worksheet and create a pivot table

 

STEP 3.  Add a calculated field to the pivot table, the formula should be =Profit/Sales

 

 

 

 

 

STEP 4.  Make use of the calculated field in the pivot table.  You’re done. 

 

 

 

In Tableau

 

Aggregate calculations in Tableau are made in exactly the same way as other types of calculations – the only difference is the functions which are included in the calculations.

To replicate the profit calculation created for Excel above, simple use the following formula:

 

SUM(Profit) / SUM(Sales)

 

There is no need to define the category by which Tableau should sum the profit and sales values, the aggregate values will be calculated on the fly as Tableau creates your defined view.  Thus, this is a very flexible type of calculation in Tableau.

 

 

 

Further examples

 

Non-aggregate calculations

 

	in Excel	in Tableau
Add firstname and last name to create fullname	=CONCATENATE(A2," ", B2)	[Firstname] + ‘ ‘ + [Lastname]
Use the first three characters to create a short code	=LEFT(C2, 3)	LEFT([Product Code],3)
Test if an individual sales transaction was profitable	=IF(B2>0, "Profitable","Not Profitable")	IIF([Profit] > 0, ‘Profitable’, ‘Not Profitable’)

 

 

Aggregate calculations

	in Excel	in Tableau
Determine if sales in a Region exceed a threshold	use an if statement in a formula that references a column in a pivot table	IIF(Sum(Sales)>400000,’Above Target’,'Below Target’)
Calculate profit ratio	create a calculated field in a pivot table (as shown above)	SUM(Profit) / SUM(Sales)

 

 

 

Further resources

 

Tableau have a great video on creating calculations on their website – you can find it here.