Tableau Table Calculations and Reference Lines: Extending the Default Panes

Data

Tableau Table Calculations and Reference Lines: Extending the Default Panes

Some fun Table Calculation questions came in this week. With four hours in the air heading home to New York from Portland, OR, I thought I’d quickly add these to the learning library. Here’s the first – I’ll put up another post with the second, which is a great extension on our Calendar Blog Post.

My client wanted the ability to show reference lines averaging across two different levels of dimension on a single sheet. Here’s a good review of reference lines. The challenge here is that Tableau’s reference line options only give three default choices – Table, Pane, and Cell. Well, Pane worked well for one of these two reference lines, but the default settings were insufficient in this case to achieve the end result. So, here goes with a quick Table Calculation Solution! Be sure to review table calcs beforehand.

I built a dataset that will demonstrate the issue pretty easily. Fairly straight-forward; we’ve got Three levels of Dimension, and later on we’re going to try to figure out an average by Region and by State, only one of which will be possible by default.

Region

State

City

Sales

Inventory Units

East

NY

NYC

100000

2000

East

NY

Ithaca

40000

4000

East

NY

New Paltz

15000

3000

East

NC

Durham

26000

2000

East

NC

Asheville

35000

4000

East

NC

Brevard

20000

2000

West

UT

Salt Lake City

30000

4000

West

UT

Moab

25000

3000

West

OR

Beaverton

26000

2000

West

OR

Bend

150000

4000

West

OR

Portland

300000

2000

So, I connected to this Excel sheet in Tableau 8.0.2 and noticed a new behavior – notice that Tableau is now automatically adding a hierarchy for State/City. I’m not sure when this behavior surfaced, but I do like the suggestion; building hierarchies is a great way to organize data and makes it easy to build quickly in Tableau.

Dimensions - Region, State and City

Anyway, I built a simple Bar chart to show Inventory by Region, State and City.

Inventory by Region, State, City

Then I added a simple Pane Average of Inventory, and we can see that Tableau calculates that at the lowest Pane possible, in this case at the State level.

State Average

Here’s the Reference Line Dialog:

Reference Line Dialog

Ok, so far so good. Now I also want to create an average at the Region level. Unfortunately, I’m out of options on the scope – I’ve already used Pane and I don’t want to create a Table average. 

The solution is a Table Calculation, using the Window_Avg function. It’s important to remember that a Table Calculation is a Post-Aggregate function – which means that it occurs after Tableau has executed the SQL query to the Excel document, but before Tableau renders the view. The take-home message is that the Window_Avg function will take an average of the City level aggregations across the ‘window’ as we define it; it will not necessarily be the same as the SQL average if you aggregated at the Region level. 

Anyway, this is the Table Calculation that I put together:

Regional Avergae window_avg Tableau Calculation

Then, we want to provide access to this Table Calculation in the View, so we drag it to the Detail Shelf, which calculates the values, but doesn’t necessarily do anything visual with the result set. We’re then going to have to adjust the Partitioning to fix the ‘Window’ to refer to Regions.

Table Calculation in the View

So, we’re going to Edit Table Calculation and change the Compute Using functionality to use the Advanced functionality since we want to take States and Cities into account, but not Regions.

Edit Table Calculation - Advanced functionality

I’ll use the settings below, and since Sort isn’t important to the Avg context, I’ll keep the setting at Automatic. Then, when we say OK, since we’ve already set up the Addressing and Partitioning correctly, we’ll use the options here how they lay.

Edit Table Calculation - Advanced functionality part 2

Edit Table Calculation - Advanced functionality part 3

Now it’s just a matter of visualizing the data that we’ve just calculated; we’re going to add another Reference Line through the standard technique, using the PANE again – although what we’ve just done will define our own Pane rather than use Tableau’s default concept.

Edit Reference Line

The result – notice that our Pane has now been redefined to describe the Region rather than the State.

Regional Average

So, pulling the two together, we get a single sheet with both Reference Lines.

Both Averages

There! We’re done, right? 

Well, sort of – there’s always a catch. The client then followed up that what they ACTUALLY wanted to do was show a combined axis chart where they not only do the above, but add on another measure, like Sales. Well, doing so (go ahead, try), will change Tableau’s default behavior to create a Pane average at the lowest level of Pane, which in my case is Measure names, so we get the average of the Sales and Inventory bars. Yuck.

Wrong Combined Axis Chart

Well, there is a solution here – it’s really an extension of the technique we’ve already used. We’re already set on the Regional Level for Inventory Units – we can do exactly the same thing we already did (which is add the field to the Detail shelf, set the addressing, and then add the Reference Line). So, we’ll need to build a similar field for the State Level – it’ll use the same formula, just different addressing. 

Here’s what I used:

Table Calculation State Industry Average

Alright! All done – I pushed this workbook up to Tableau Public, and you can download it from there or through the download link below. I added a few extra calculations including Deltas from those averages, which are potentially where you’ll want to go in terms of making this data actionable.

Also, here’s a picture of sunrise from Mount Hood’s summit last weekend because if you haven’t been recently, you should go ski mountaineering (or wine touring) in Oregon!

More About the Author

James Wright

Global BI Practice Director
Which Tableau Support Is Right for You? If you own Tableau Software, the good news is that you have access to the industry-best business intelligence platform capable of ...
Unexpected Surprises: IMDB Movie Analytics I haven’t blogged much recently, but I wanted to quickly share a really great moment. Onsite with one of our long-term and larger NYC ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072