Tableau Tip Tuesday: Don’t Use Table Calcs

• By Joshua Milligan
• Posted in All, Calculations, Tableau Tip Tuesday, Tips and Tricks
• On January 20, 2015
• 4 Comments.

No, not really!  I love table calculations! They are powerful and very useful.  They are one of the best things in Tableau.  Don’t be afraid of them and don’t avoid them, except…

• Avoid table calculations when a simpler aggregate calculation will work.
• Avoid aggregate calculations when a simpler row level calculation will work.

Why?  Because you lose control, flexibility, and efficiency.  When I first started to get the hang of table calcs, I found myself using them to solve every problem.  But that’s not the best approach.  The bottom line for this tip: keep it simple!

Row Level Instead of Aggregate

If you use an aggregate calculation, when you could have used a row level, then you will never* be able to use row level logic on that calculation.  Additionally, aggregate calculations must be computed at run-time.  Row level calculations may be materialized in an extract, meaning that they are only computed once.

*the only exception is when you use a Set or Level of Detail calc

Example:
You want the cost of goods sold when you have sales and profit.

Don’t do this:

You won’t be able to change it to an average cost of goods sold or compare a minimum and a maximum in the same view.

Instead, do this:

As a row-level calculation, you’ll be able to specify the aggregation in the view.  It might also get materialized (computed once and stored) in an extract which means better efficiency.

Exception: When you specifically want to limit a designer using a published connection or web editing to a specific level of aggregation (e.g. you always want the designer to use average Cost of Goods Sold.)

Aggregate (or Row Level) Instead of Table Calculation

If you use a table calculation when you could have used a simple aggregate, then you introduced a level of complexity that was not needed and made it harder for others to understand.  Furthermore, table calculations are calculated based on field arrangement in a view. So any changes to the level of detail or layout of the view can fundamentally change the way they work.  Simple aggregates are done at the level of detail defined by the dimensions in the view, but the fundamental calculation remains unchanged when you rearrange, add, or remove dimensions.

Example:
You want to add a reference line for the SUM of Sales for Appliances across the table to give a visual benchmark.

 

Don’t do this:

Even if you can make the assumption that Appliances will always be first in terms of Category, how can you be certain that it will always be first based on the scope and direction of the First() table calculation when other dimensions are introduced?

Instead do this:

As a simple aggregate, the calculation is less brittle and can be performed at the data source level.

Or, do this:

Each row will keep the value for sales if it was an “Appliances” row (and NULL by default otherwise).  As a row level calculation, the results could potentially be materialized in an extract and is the most flexible.  You can choose to aggregate later.  And you can choose how.

When creating calculated fields, always look for the simplest approach.  Check back next Tuesday for another Tableau Tip!