Some Tableau Tips & Options for Box and Whisker



I recently tweeted that Box and Whisker plots have never been my go to DataViz type.

I'm warming up

I don’t know what it is, but they’ve never appealed to me.  Maybe statistical terms like median, percentile and “1.5 times IQR” are just a layer of abstraction that I’d rather avoid.  Maybe I have a fear of funny whiskers.  Maybe it’s because a standard view tends to have overlapping marks that obscure how many observations are present.

In fact, this is the “standard” box and whisker, here showing the number of orders for products within some selected sub-categories:

(I've always found this hard to read - maybe it's just me)
(I’ve always found this hard to read – maybe it’s just me)

And you’ll notice in the bottom status bar that there are 502 marks.  But count the circles in the view.  Do you see 502?  No, because many are on top of each other.  So how many outliers are there?  One… ten… fifty?

Now, there are some ways to counter this.  One way is to add some transparency to the marks so that ones off by themselves are light and ones that are on top of each other appear darker.  I’ve often resorted to “jittering” – intentional or random noise to the view to spread the marks out a bit, like this:

I'm feeling a bit jittery.
I’m feeling a bit jittery.


We’ve still got the 502 marks, but now they’ve been spread out vertically (using the Index() function in this example) which allows us to get a better idea of how many products there are in each Order bucket.

This isn’t bad, but it’s still hard to see much difference between some of the # Order values.


A Tableau Innovation

Then Alexander Mou (@Aleksoft) demonstrated a way of creating overlapping box and whisker with a histogram.  Now, I’ve always been a fan of histograms.  To me, they are much easier to understand.  Now I can have my cake and eat it too.  Because the histograms are there and are enhanced by the box and whiskers do give additional information.

Check it out:

It's a histogram!
It’s a histogram!

This builds on the “jittering” concept – but instead of assigning a vertical position for each circle mark, the marks have been changed to bars of height 1 and then stacked on top of each other.  In fact, if you click on a bar, you’ll find that each bar is really made up of height 1 segments (one for each product with that # of orders).  This is great – a histogram with added value!


Extending it for UI (LODs to the Rescue)

This approach is brilliant and yields an incredibly useful viz.  The only issue I have is when I want to use it on a dashboard with certain kinds of interactivity. The stacking behavior that was innovatively leveraged to build the histogram can work against us.

What happens in the dashboard below if I want the user to be able to click on any bar to see the products for a given subcategory with a given number of orders?  Try it in the top view in this dashboard:


Using the stacking method histogram causes a click on a bar to be interpreted by Tableau as a click on a single segment.  Additionally Tableau cannot use the # Orders as an action filter because it is a Measure (aggregate) – and only dimensions can be passed as filters in actions.

So, we simply modify the view using Level of Detail (LOD) calculations.  FIXED level of detail calculations can be used as dimensions – which allows us to determine how many orders per product and ultimately pass that as an action filter.

LOD Calcs to the rescue

The view we build is a bit more complex, using a dual axis (so the box and whiskers can be displayed for all the products, while the bars on top – which the user will interact with – will not be split by product).


Go back to the dashboard above and try the actions with the lower view.  Ah, that’s nice…


One final thought for box and whisker

Alex’s work led me to think of one other way to handle distributions.  Instead of jittering or bars, what if you sized the circles, like this?

“Judge me by my size, do you?” -Yoda


You’ll notice that the circle size now corresponds to the number of products with that number of orders.  This is accomplished using the Size() function and partitioning by the number of orders.  I can partition table calculations using discrete aggregates – so I copied the # Orders field from Columns to the Detail of the Marks card (since I still wanted it to be continuous for an axis on Columns), made it discrete and unchecked the “Ignore in Table Calculations” option.

This view could also be improved with LOD calcs so that instead of 502 overlapping circles, we’d get one circle for each # Order bucket.  Whether or not I go to the effort of doing that depends on how I want to use the view.


What do you think?  Which option do you prefer?  Do you have any other options? Leave a comment.

Box and Whisker Options


Games In Tableau – Pushing the Limits with Practical Results

You may not think of Tableau as a game engine, but I’ve used it to create interactive games (such as tic-tac-toe, Star Trek, and Choose Your Own Adventure) that push the limits of what is possible while learning some very practical lessons.

In my #Data15 presentation: #GamesInTableau, I demonstrated numerous practical tips, tricks, and principles of interactivity and storytelling that can be applied to any dashboard. Some of the principles are simple, others are complex, but everyone can come away with something that can be applied to their Tableau work.

The recording of the session will be made available by Tableau, but in the meantime here are the slides and workbooks I used:

Download Slides

Download Workbooks

#Data15 Here I come!

It’s time for the Tableau Conference! (a.k.a. #data15 or TC15)

If you are going, I look forward to seeing you there.  As I scramble to make last minute preparations, I thought I’d share a bit of what’s been consuming my focus:

1. Presentation: #GamesInTableau

Data loves to play.  And I love to play with data – especially in Tableau.  And it’s Vegas!  So, join me on Wednesday morning and get ready to play!

Data Loves to Play

Also, I’ll be posting presentation materials so they are up by the day of the presentation.  So check back!


2. Perfect Timing

My publisher wanted to know if I’d like some promotional codes to share with blog readers in case they wanted to pick up a copy of the book Learning Tableau. An early conference gift!  Here are the codes (click to go to the site and then enter at checkout):

Learning Tableau Discount

By the way, if you happen to have a copy of my book, bring it to conference.  I’d be happy to autograph it!


3. Teknion BI Trailblazer

Teknion Data Solutions has a cool new tool to help you determine your BI needs and next steps for your organization.  The best part?  It’s 100% created in Tableau – so lot’s of fun to make.  Well, that’s the best part for me.

The best part for you? You come away with a better understanding of the best BI path to take… plus I hear there’s a drawing for an iPad if you complete the assessment by conference end!


And come by Teknion’s booth #664.  I might just be there!


4. Tableau Doctor

I get to dress up in a white lab coat and play doctor for a few hours!  Okay, I don’t about the coat., but I’ll be on call at conference and ready for your appointments!  Check back and I’ll update with date/time!


5. Everything Else!!!

And then there’s the packing, scheduling, making connections, trying to pick between dozens of awesome sessions to attend, and trying to remain calm when thinking about what new Tableau features might be teased.

If you are going to be there, let me know.  I can’t wait to see you!



Tableau and Cubes (OLAP)

At some point, I got labeled as a “Tableau cube expert.”  The funny thing is, when I first acquired this label I just barely knew how to spell OLAP.  But over the years, I’ve gained some experience working with Tableau and cubes.  And I get questions almost weekly about how Tableau works with cubes and for some Tableau tips and tricks for working with cubes.  Here are my thoughts:

This represents what happens to my brain when I try to understand MDX
This represents what happens to my brain when I try to understand MDX

The Cube Paradigm

Tableau works with cubes by translating VizQL (the Visual Query Language Tableau generates based on where you drag and drop fields in the view to visually encode the data) into MDX and issuing that as a query to the cube, getting back the results, and then rendering the visualization.

Tableau works with cubes using the same overall paradigm as any other data source.
Tableau works with cubes using the same overall paradigm as any other data source.


When cubes are well designed and when you’ve got a developer or team of developers who are pretty quick to turn-around requests for cube enhancements, it can be a fairly enjoyable experience to work with Tableau against a cube.  The key to understand, is that cubes are designed to “pre-aggregate” the data, meaning that all the aggregations are defined already (so if it is an average in the cube — that’s what you get!) and the values are stored for each intersection of dimension values.


What this means is that the cube is where most of the analytical logic must take place.  When you are working with relational data sources, Tableau offers a wealth of options for implementing business rules, logic, and analysis: calculations using dimensions, creation of new dimensions, groups, bins, LOD, Top N Filters/Sets using an aggregation of the dimension itself, custom aggregations of any row level field, complex calculated filters, data source filters, view underlying row-level data, etc…, etc…, etc…


But those are not options with a cube.  Why?  Because cubes are designed to handle all of that themselves.  In fact, most of it is pre-calculated and stored in aggregate form — so you can’t simply create new slices of data or new custom aggregations. That takes away a lot of complexity from Tableau (so give a business executive a connection to a well-designed cube with business rules baked in and a license of Tableau and she’s pretty safe in the analysis she does), but it also takes away a lot of flexibility.


There’s still a lot you can do with Tableau — statistical functions are mostly there (sans forecasting), table calcs (sans referencing dimension fields – but I can’t think of a reason why the table calc shouldn’t be able to reference a dimension – hence: this idea for which you can vote)  And, of course, you can visualize what’s in the cube.


But if you find yourself needing to do analysis using fields not in the cube or want to implement complex business rules or logic – then be prepared to implement that in the cube.  Or use another data source (after all, the cube was built from something!)


Tips and Tricks for Using Tableau with Cubes

At the same time, I have discovered a few tips, tricks, and workarounds that may help you should you find yourself using Tableau with OLAP:

  1. You cannot create an extract from a cube, but you can create an offline cube file (.cub or .cube) and use that as a data source and even distribute it as a packaged workbook (.twbx).
  2. You can write calculated members using MDX.  This can help you group dimensional values, among other things.
  3. Make sure that in addition to having a date hierarchy in the cube, you also have a date dimension with the actual value of the date. That will give you ultimate flexibility in choosing how you visualize dates.
  4. If you can’t change the cube and are stuck with just the date hierarchy, then create a secondary source with the date parts and the full date and then blend to get the exact date value.
  5. For that matter, use blending to get values not available in the cube. Frustrated that you can’t alias dimension values when using a cube?  Just create a secondary data source with the actual values and desired aliases then blend and use the alias.  Sad that you can’t create ad-hoc or calculated groups?  Create a secondary source with the groupings and blend!
  6. Use table calculations to fake calculations that you can’t do. For example:


If you wanted to get the difference between Profit for Technology and Office Supplies, you can’t write the calculation:

IF [Category] = “Technology” THEN [Profit] END
IF [Category] = “Office Supplies” THEN [Profit] END

But using the view above, you could write:

LOOKUP([Profit], Last()) – LOOKUP([Profit], First())


7. Try to convince your boss to use a nice star schema data warehouse instead.

Cube resistance is futile!
Cube resistance is futile!


Never Ending Navigation: Buttons that Deselect Themselves

Navigation buttons can make a series of dashboards easier to use.  The most common approach has been using a view that shows an image, shape, or text for the user to click to trigger an action that filters into another dashboard.

The main issue is that once a mark in the navigation view is selected (which triggers the action), then if the user goes back to the original page, then they have to first deselect the mark before they can trigger the action again.

Check out the issue here by navigating between the sheets a few times:


And now that you’re frustrated, consider some of the options:

  • Use a hover action. This actually works nicely in some cases, but can be accidentally triggered all too easily.
  • Use a Story. Another nice option in many cases.  However, you are dealing with snapshots of dashboards and vizzes, so be aware that things like global filters and parameters won’t carry through from one story point to the next.  Still, consider this option and apply it where it makes sense.
  • Instruct the user to double click the navigation button. Since the first click selects and the next deselects, a double click will work… if the user is fast enough so the action doesn’t navigate before the second click.  And I’m not too fond of telling the user to do something outside the normal use patterns.
  • Give up. Don’t do this.  Anything can be done in Tableau.  There has to be a way.  I just know it.  And there is.  Remember how the “Fire” button disappeared in the Star Trek Tableau game?  There’s a clue in that behavior that we can exploit to get the navigation working the way we want.

NeverEnding Navigation! That’ll make anyone smile!

The NeverEnding Data Story
The NeverEnding Data Story


Use a Navigation Data Source

This is important, because we’ll want a data source that we can apply a global filter to and not mess up other vizzes.  It’s also good practice to use separate data source(s) for things like navigation buttons, information and help buttons, and the like because they can be kept small to avoid a performance hit (I learned that the hard way when working with a hundreds of millions of rows data set and I used it as the source to create a simple help button viz).

Here, we’ll just use a source with a single column (ID) with rows 1 to 100.  We could get away with less rows (as few as 2), but the fewer the rows, the higher the chances that we’ll end up hitting a previously selected value that will frustrate the user.

The only other thing we’ll do is create a calculated field to increment the ID until we hit 100 then we’ll reset back to 1:

Navigation Calculation

If Tableau categorizes the calculation as a measure, re-categorize it as a dimension. This is necessary because we’re going to use it for an action filter and Tableau requires dimensions for action filters. (Thank you Jason Scarlett for pointing out this clarification in the comments below!)


The Views

It’ll depend a little on your navigation scheme.  Here, I’m keeping it simple with a view for forward and another for backward navigation.

Here’s forward:

Forward View

I just put the calculated field Increment ID on the Detail of the Marks Card and set the mark to Shape (right arrow).  The back view is identical except that I used a back arrow shape.  I won’t worry about the fact that I’m getting a mark for each value of Increment ID.  We’re going to use actions to filter, so I’ll let the action filter take care of everything.


Adding Navigation to the Dashboards

So, the first I thing I do is add the views to each dashboard.  You’ll once again notice the multiple marks for forward and backward.  That’ll get cleared up in a step or two:

Profit Dashboard

Once I get the views added to each dashboard, I’ll setup the action.  From the Dashboard > Actions menu, I’ll add a Forward action and a Backward action on each applicable dashboard.

Here’s the setup for the action:


Several notes:

  1. It will be on the selection of the button from one dashboard as the source and the next (or previous) as the target.
  2. When the action is cleared, I will Leave the Filter
  3. I specifically map Selected Fields – and this is one of the secrets: map Increment ID to ID. Why?  Because that will mean that each action will filter the navigation to the next ID – meaning that a new, deselected mark will be drawn.  So when I click the forward button on one dashboard I’ll navigate to the next with forward and backward buttons for ID 2 which in turn will trigger actions to filter the next buttons for ID 3 and so on, until we reset upon reaching 100.


Once you have the action set up for one dashboard, go ahead and trigger it.


And now, the final two Tableau secrets:

First, find the drop down caret on one of the navigation button views (upper right of the border around the dashboard object) and use it to show a quick filter for the Action (ID).


Then, once you have that quick filter, use the drop down caret on it to apply the action filter to All Using This Data Source.  So, this one action will be universal to all sheets using the data source – including the one that triggered the action!

Apply to All


Now, just go through the remaining dashboards and setup the actions for all the buttons.  And you are done!

Here’s an example (and it uses the old Hide Control Filters in a Secret Place trick so you can see how the filter is working in Desktop if you download the workbook.)



Yes, it’s a bit more setup that the traditional way of adding navigation buttons – but you’ve just made life a whole lot better for your users.

Let Them have Their Text Tables

“I need you to reproduce this report,” your boss states as he hands you a printout. “And you can use Tableau!” For a second you are excited at the thought of using Tableau. But your excitement fades when you see the report you must reproduce:

At least there aren’t any pie charts.
At least there aren’t any pie charts.


It’s just a text table.

So, you point out to your boss that a little visualization of the data would tell a good story and bring the data to life.

Your boss considers this for a second and then responds that the table does tell a story.

“See,” he says. “I can very easily follow the story across the table: where we were last year, how many we hired, how many we fired, and where we ended. Now build the table before I decide to add 1 to the Involuntary Separations column!”

Generic Mean Boss
This isn’t my boss. My boss is nice. Steve, if you are reading this, please give me a raise.


And there you are. In no time you’ve reproduced the cross tab in Tableau and saved your job:

A Tableau Text Table

But you feel like you could have done more. You should have done more. You are a data artist not a report factory worker!

Give them what they want… and more!

Sometimes you don’t have a choice. And sometimes a text table does serve a purpose (e.g. providing a way to lookup values or validate results). But you don’t have to stop at the simple table.

Look for opportunities to take that text table and add visualization elements that enhance the data story and help transition your audience from the old “I love rows and columns of numbers” crowd to the “bar charts are our friend” group. Let them have their text tables for now… but give them even more:

Chart in a text table

Your boss was right, the table did tell a story. So, give him the table he wants, but help transition him to the world of data visualization by adding some visual elements. The bar charts only add to the story and give the ability to easily compare the plain numbers.

The Best Part

The Best part is that it is (relatively) easy to accomplish. It’s just two views (1 – The original text table and 2 – a chart with the same rows/columns) in a dashboard with the 2nd view floating on top of the table. Here’s the second view from above:

The second view

And here it is, with the header for Employee Type and the axis hidden, floating on top of the text table:

It's floating

Yeah, it took a little effort to match the formatting and line things up. But you have the pixel perfect positioning and sizing controls for floating objects, so it wasn’t too bad.

And you can experiment with different variations:

Stacked bars in text table

And now, you’ve kept the text table fan in their comfort zone while helping them see that visualization isn’t too bad.

“In fact,” your boss says, “it’s kind of nice. Wait, you learned that from a blog post?  Where can I Subscribe?”

Dynamically Set Default Values for Quick Filters in Tableau

Recently, I was helping someone with a Tableau dashboard in which an action from the first view filtered a second view.  The second view additionally had a quick filter and that quick filter set to show only relevant values.  Something like this:

But, try setting the drop down quick filter option to “Medium” and then select “Express Air”.  Notice that “Medium” never occurs with “Express Air”.  The result is all data filtered, a blank view, and the drop down filter shows “(Medium)” – it’s in parentheses to let you know that the filter value isn’t relevant.

And, I can confirm that with a quick view:


Indeed, not all ship modes use all priorities.

Now, the question arose: Can we get the quick filter to default to a certain value based on the Ship Mode selected?  That is, can we set a default value for a quick filter based on an action?

Don’t be too quick to say, “No!”  That was my first inclination.  But I was wrong… There is a way!

And I’ll tell you next time…








…Just kidding!  I’ll tell you now:


Setting Default Values for Quick Filters

Let’s say I identify the following values that I want for the defaults of the quick filter drop down based on my selection:

desired default values

Here are the steps I took:

  1. Create a field that gives me the default order priority for each Ship Mode. I could do this in the source.  I chose to do this as a row-level calculation.  It’s important that it’s row level and not aggregate, because I’ll need to eh field to be classified as a dimension (so no blending for the default either).

Calculated field to set default for each Ship Mode

  1. Make sure the field is in the view that will trigger the action. Here, I’ve just added it to the detail of the Marks card:

Add to Detail


  1. Then I create a new, separate action in the dashboard that maps Default Order Priority from the source sheet to the Order Priority field of the data source for the target:

Action Filter Options

Notice that I’ve specified the Target Filters to specifically map the Default Order Priority to the Order Priority.


After triggering the actions on the dashboard, you can look at the target view sheet and see that the Actions are applied as filters:

Action Filters in the View


So, the final trick to dynamically set and change the quick filter value is to use the Action filter as a quick filter instead of the original field.

To do this, locate the little drop down caret and use the menu to add the quick filter for the Action:

The little drop down caret

And now, you have a dynamic quick filter with default values based on the selection!  Experiment below:



Star Trek: The Tableau Game

In the 1980s, my dad brought home a Tandy 1000.  I still remember him giving instructions to my mom and me about how to use DOS.  I loved playing the games (Digger, Janitor Joe [Jump Joe], Flightmare, Hopper, and more), but the real fun began when my dad showed me I could look at, and even modify, the code for games written in BASIC:

BASIC Star Trek Code

Star Trek was the BASIC game that sparked my interest in computer programming (and yes, I realize how much of a geek that sentence makes me).  It was a simple game.  An overall grid of sectors in the quadrant broken down into smaller grids where Klingons and an occasional Romulan lurked.

You can only imagine how much fun my 8-year-old self had when I realized that the E = 3000 on line 370 and the P = 10 on line 440 were the units of energy and number of photon torpedoes.  After that, the Klingons didn’t stand a chance.


What I loved about programming was that I could make the computer do anything.  There was no limit to the problems that could be solved or the fun that could be had.  And that’s why I love Tableau.  The same sense of “the-sky’s-the-limit” that I felt when I first discovered coding is the sense I feel every day when I use Tableau.  With the added bonus of intuitive drag and drop (okay, sure, there’s a calculation or two).  But there’s no end to what can be done.

And so, I decided to take my inspiration from the old Star Trek game and give it a Tableau twist.  Whereas the original game was text-based, Tableau is of course graphical.  That opens up some really neat possibilities.  I’ll reserve future blog posts to cover the technical details, but here are a few really cool things that push the envelope in Star Trek – the Tableau Game:

  • Everything is done entirely in Tableau. I love possibilities with the JavaScript API, but everything here is native Tableau.
  • Notice how the “Fire!” button disappears as soon as you click it. That’s right, it’s an action that applies to the sheet that triggered it!  I stumbled across this behavior accidentally – I’m going to explore it more and likely have a blog post or two – stay tuned.
  • The biggest challenge was how to keep track of past actions. How could I know how many Klingons the player had destroyed?  How could I track shield and life support values?  Parameters keep only a value at a time and can’t be updated with actions.  Action filters only contain the most recent values selected.  The answer is… another blog post for another time… or maybe an AMA on the forums?
  • The original BASIC Star Trek game was 553 lines of code (here it is if you are interested). The primary Tableau data set is 20 records.


And now, without any further tedious details (at least for a while):


Space: the final frontier. These are the voyages of the starship Enterprise. Its five-year mission: to explore strange new worlds, to seek out new life and new civilizations, to boldly Tableau what no one has done before.

10 Things you didn’t know about Ad-Hoc Calculations in Tableau 9

Ad-hoc calculations are new in Tableau 9.0.  They’re quite useful.  And they are fun!  There’s so much you can do with them.  They are indeed one of my favorite things.

Here’s a  quick start from Tableau to get up to speed.
And, once you’re ready, let’s dive deep for some Tableau tips and tricks using ad-hoc calculations in Tableau 9:

  1. Create Ad-Hoc Calcs by dragging text from the Calculation Editor into the view or onto shelves


Drag and Drop from Calculation Editor


  1. Do the reverse: Drag and drop ad-hoc calcs into the calculation editor
  2. Drag and drop the ad hoc calculation from the view to the Dimensions or Measures in the Data pane to actualize them as calculated fields in the data connection.
  3. You knew you could create an ad-hoc by double clicking on Rows, Columns, or in the Marks card. But did you know you can create them on the Measure Values shelf too?
  4. Create titles for views. Just double click a space on Columns type the title inside quotes and do some formatting.  Why would you do this?  Check out this post.

Create Titles


  1. Drag and drop fields, sets, and parameters into ad hoc calcs. Just start the ad-hoc calc, then drag from the Data pane or from anywhere else in the view:

Drag and Drop Into

  1. Double click any field in the view to start an ad hoc calculation. If the field is from the connection, the code starts with a reference to that field.  If it was an ad-hoc calculation then you pick up editing where you left off.
  1. Use them to sort a view, even with blended fields and calculations that don’t always play nice with sorting. Here I copy the field (hold Ctrl while dragging) I want to use for sorting from Columns to Rows, double click it to Edit it as an ad hoc calc, place a negative sign in front to sort the direction I want, change it to discrete and move it to the front so it defines the sort.  You could do similar things with ad hoc calculations using the Rank() function.

Sort with Ad Hoc Calculations in Tableau

  1. Multi-Line Ad-Hoc Calcs. While typing an ad-hoc calc, press Shift+Enter to start a new line.  Caution: the field will only appear with the first line showing, so this is a great way to confuse your co-workers.  However, it can be used to make things clearer – see #10:


  1. Named Ad Hoc Calculations. Using the mult-line approach works really well if you make the first line a comment (i.e. start with the double slash: // ).  Why?  Because then the ad-hoc calculation gets a name which will show in Row or Column headers.  Both of the fields on Rows in the view below are ad-hoc calcs with the code
    City + ", " + [State]

    but the second one is mult-line with the code

    //City and State
    City + ", " + [State] 

Named Ad Hoc


And now you know 10 things you never knew before about ad-hoc calculations in Tableau.

Or maybe you did know these.  And maybe you know other tips and tricks I didn’t mention.  I’d love to hear from you in the comments!

Extending LOD Calcs with Table Calcs

I’ve been following with great interest the discussion in the Tableau community surrounding various approaches to using Level of Detail Calculations (LOD calcs) to solve various problems.  On the one hand LOD Calcs are often very easy and intuitive.  For example, it easy to determine an aggregation at any level of detail:

  • How many unique customers per product?

{FIXED [Product] : COUNTD([Customer ID])}

  • Maximum sales of a Category per State?

With State in the Viz Level of Detail: MAX({INCLUDE [Category] : SUM(Sales)})


But what if I want to know what the maximum category actually was?  That’s a little harder.  Mark Jackson recently posted on how to find a dimension at a lower level of detail along with a follow-up post based on an approach suggested by Joe Mako.  Jonathan Drummey had a similarly outstanding post which demonstrates his thought process in tackling such a problem.

What I’d like to do here, is replicate my thought process in solving a similar problem.  M thinking has been very much shaped by interactions with Mark, Joe, and Jonathan and my approach is very similar – but I find it very useful to see the slight variations in the different ways individuals approach similar problems.  And I’m going to extend the results a little to explore what kinds of analysis are possible after solving the initial problem.  I very much welcome any feedback!


The initial problem

Let’s say I want to know the Category with the most sales.  We’ll start by assuming I want to know the highest selling Category in a Department.  But later, I might want to swap Department for any other dimension (such as State or Region).

(follow along with this workbook)

I’ll start with Department and Category in the view, although ultimately, I don’t want Category in the view.  But having it there initially will help me see how things work at that level of detail:

Sales by Department and Category

First, I need a Level of Detail calculation that gives me the maximum Sales for all Categories in a given Department:

Nested LOD

The hardest part of thinking through nested LODs is that I started with a view that is at a different level of detail than what I ultimately want.  Here, I already have Category as part of the viewLOD (view Level of Detail).  I’ve done that so I can see the results unfold before me, but I have to keep in mind that I’m going to remove it later, so I start with an INCLUDE to get the Sum of Sales for each Category.

Then I want to back up to the Department level to determine the MAX of all the Sums of Sales for each Category within a given Department.  So I EXCLUDE Category and find the MAX.

Now, I have the Sales of the highest selling Category within each Department and it’s easy to see which Category has the highest sales for the Department (it’s the one where the SUM(Sales) matches the MAX Sales):

Figuring out the Highest Selling Cateogry

So, I’ll write one more calculation to determine which Category has a Sum of Sales that matches the Max.  Here it is:

Find the match

The trick here is to use INCLUDE to make sure I’m doing my comparison at the Category level (just like I did visually in the view above).  Since I want this to work even when I remove Category from the view, I’ll INCLUDE it here.

And now, I can remove Category from the view and only see the Max Category for each Department:

Initial results of LOD

Sales here are the total sales for each Department, but I also have the Category with the most sales and the actual sales for that Category.

Extending the example

The best part is, I can add other dimensions and even swap other dimensions with Department.  For example, here is the highest selling Category for each State:

Max Category per State

And I can even re-arrange the view to get other types of visualizations, such as this map:

Map of Highest Selling Category per State

And I could use this same approach to answer other questions (such as, which category has the lowest profit in the east?)


But let’s take it one more step…

What if I wanted to know how many states for each Max Cateogry?  In how many states were Office Machines the highest selling Category?  I could count them visually in the map or in view like this:

Max Category with States

But, even better I can let Tableau do the counting and then visualize the results!

If I allow the Max Category to partition table calculations (simply right click the field on Rows and uncheck “Ignore in Table Calculations”).

This is the best part of the LOD calcs – they are aggregates (or sometimes even row-level) results that can be used to partition table calculations.  That gives us another layer to solve very difficult problems without having to layer complex table calc on complex table calc.

Ignore in Table Calculations


Then, the Size() function (computed along State – right click the field in the view and select Computer Using > State) will tell me how many states per Max Category:

Working out the Table Calc

State has to be in the vizLOD for the Size() function to return the right results.  But I don’t need to see the same result repeated state after state, so I will use the First() function (also computed along State) to keep only the first result of Size().


Moving, First() to Filters, State to Detail (it has to be in the view, but I don’t need to see it) and Size() to Columns allows me to have a view like this:

How Many States where X was the Highest Selling Category?

Wow!  Tables were the highest selling category in 11 states!  I should double check profit…


Everything up to the Extended Example could have been done fairly easily with table calcs.  However, using the LOD calcs allowed us to further partition table calcs that allows our analysis to go even deeper without having to get into incredibly complex table cacls.



I knew I loved LOD calcs when I first saw them.  But the more I work with them, the more I see the possibilities…

Tableau Tips and Tricks ● Story Telling ● Beautiful Data Visualizations