## Tableau for Excel users – Part 2 – Calculated fields

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 calculationin 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:

Order ID | Sales | Profit | Profit Ratio (NA) |

1 | £100 | £20 | 20% |

2 | £1,000,000 | £500,000 | 50% |

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.

Great post guys. It’s one of the first things that I try to get people to conceptually understand. “If you were doing this in Excel, would you have a new number on every row in a new column, or would you have a single number at the bottom of the columns?”

Because we are once removed from the raw data in Tableau compared to Excel, it’s so critical for people to get this difference.

Good point about being removed from the data – I hadn’t realised that is probably why this can sometimes seem complicated.

Cheers, Tom.

I have used a parameter to make a chart show either sales or revenue. How can I format my metric in a calculated field so that I see “£” when I select revenue and not when I select sales? Cheers guys!

I assume you’re trying to get that to appear in the tooltip? A trick around this is to create a calculated field. IF (Parameter=[Revenue],”$”,””) and then put that calculated field in front of the metric in the tooltip. Be sure to include the new calculated field in Level of Detail.

Brilliant! problem solved! Thanks Mike

How to replicate weibull function from excel in Tableau?

In Excel I have paid date column headers and totals underneath each for certain facilities. I have a percent change over prior column that takes the latest paid date and divides it by the week before. How do I calculate this in Tableau?

Hi Mary,

based on the description of your challenge, I think the table calculations will help you in Tableau.

Please see e.g. the following links for further information.

http://kb.tableau.com/articles/knowledgebase/using-table-calculations

http://www.theinformationlab.co.uk/2011/12/07/making-table-calculations-work-in-tableau/

Laszlo

how the Pofit Ratio calculated returning us the SUM(Profit) / SUM(Sales) as a percentage value

Hi Shrey,

You would use the formula as you described, to get it to appear as a percentage by changing the number formatting options. You can look into them on the Tableau Help pages here: https://onlinehelp.tableau.com/current/pro/desktop/en-us/formatting_specific_numbers.html

The images in this article are no longer showing up. Part I images are fine.

Thanks for sharing