Nested Calculation – A Tableau Puzzler

Tableau is a powerful tool for visualizing data.  It is easy to throw data into, to start exploring data and to begin creating charts.  But there is a lot going on “under the hood” and more advanced analytics requires a bit of a learning curve to get Tableau to do what you want it to do.

Anyone who has worked with Tableau for any length of time will run into “puzzlers”.  Something that, on the face of it, logically should work.  But it just doesn’t!  This can lead one down a “rabbit hole” in search of an answer from colleagues, Tableau’s community forum, Tableau blogger sites, Tableau’s online manual and, finally, Tableau’s (most excellent!) support.  Hours can be spent on what, again, on the face of it, should be a simple calculation.

But once learned, never forgotten, right?

Here is one we ran into recently, the answer to which hopefully saves other Tableau users from such a “rabbit hole” adventure.

The setup

Envision an Excel table which shows data for two variables (X and Y) from two machines (A and B) over time (in 1/2-hour increments).  Something like this, a “short and wide” table:

Now suppose we want to sum variable X (Y) over time for each machine, express these sums as a ratio for each machine and then calculate the average ratio across both machines.  We can easily do this in Excel to yield:

Now what about Tableau?

Following how this is done in Excel, in Tableau, we would:

1. Put time on the columns shelf and the “dimension” machines on the row shelf;
2. Create a calculated field for the sum of Var X as window_sum([Var X]) and do the same for the sum of Var Y (window_sum([Var Y]);
3. Place Sum Var X and Sum Var Y on the columns shelf, right click on the “Δ” in the measure value pill and set each to “Compute Using” Table (across) since we have time going from left to right;
4. Filter to show only the last time increment using last()==0 set equal to “true”.

This yields the following view:

So far, so good.  Now we can add the ratio of Sum Var X to Sum Var Y by creating another calculated field [Sum Var X]/[Sum Var Y], adding it as the 3^rd measure value and set “Compute Using” to Table (across):

Good.  Now the last step is to find the average (Sum Var X / Sum Var Y) across the two machines.  Which from Excel, we know is 12.18%.  So, let’s create another calculated field as window_avg([Sum Var X / Sum Var Y]), place it as our 4^th measure value and set “Compute Using” as Table (down) since now we want the average across machines:

Uh oh.  What happened?  We are not getting 12.18%.  Logically it should work…compute using Table (down), right?

Nested calculation

The average of (Sum Var X / Sum Var Y) is a “nested table calculation”.  If you right click on the “Δ” in the measure value pill for Avg (Sum Var X / Sum Var Y) and then click on “Edit Table Calculation” you will see the following:

Avg (Sum Var X / Sum Var Y) is a nested calculation because Sum Var X and Sum Var Y are themselves calculated values (window sums of var X (Y)).  Okay, so what?

Well, to get “Compute Using” Table (down) to work, you need to tell Tableau…and here is the trick…how Sum Var X and Sum Var Y are to be calculated inside the nested calculation.  This is true even though you have already told Tableau how to compute Sum Var X and Sum Var Y … we did this above … Table (across).  You need to do it again inside this Table Calculation dialog box.

In the Table Calculation dialog box, under “Nested Calculations”, click the upside-down triangle next to each of the components of the nested calculation (Sum Var X and Sum Var Y).  Then, for each one, select the correct “Compute Using”, which we know is Table (across).  Then, for avg(Sum Var X / Sum Var Y) choose Table (down).  Close this dialog box and you will then show the correct calculation:

Other tips

Note that if Sum Var X and Sum Var Y were not calculated values but rather “raw” values, then avg (Sum Var X / Sum Var Y) would not be a nested calculation.  Setting “Compute Using” to Table (down) without going into the Table Calculations dialog box would have worked in this case.

Another tip (from Tableau support): if you right click on the “Δ” in your measure value pill, select “Compute Using” and do NOT see any of the options already selected, then you know you have nested calculation.

So, if a calculated value is based on other calculated values, then you are “nested” and you need to pay special attention to how they are used in Table Calculations.

Hope this helps!