Two Sequential Color Palettes on the Same Map: Coloring by a Dimension and a Measure

Back in 2014 I was inspired by this New York Times post The Most Detailed Maps You’ll See from the Midterm Elections to try to figure out how to replicate those maps in Tableau. The maps essentially uses three color palettes on the same map, blue for Democratic leaning areas, red for Republican, and purple for tossups where the lightness (relative light/dark) of each color palette is based on the population of the precinct. So we might think of this in Tableau as coloring by a dimension (really a discrete value) of red/blue/purple and then shading that by a measure (the population), however that’s not something available in a couple of clicks.

This post covers a variety of techniques to build such a map, to simplify for this post we’ll build this map where there are two color palettes


For this example the data set is Superstore Sales with some filters applied to create more variation. A set of states have been arbitrarily grouped as Red and another set as Blue, and the goal is to shade them within the Red and Blue hues by the % of Total Sales. I added some additional requirements:

  1. The lightness of the colors in each palette must be equivalent (symmetrical) so the “darkest” color in each color ramp corresponds to the highest % of Total Sales regardless of which hue the color is (red or blue). This allows the user to compare the relative lightness across the hues, if we don’t then then the visualization will lead to inaccurate conclusions.
  2. The color ramps for the palettes must automatically update as the data changes, for example if a user filters the view or new data arrives.
  3. The color legends themselves have to be accurate and appear in the worksheet. This is an ease-of-construction requirement because if we’ve used a technique that hacks the color legends too much then we’d have to manually build a color legend and put that in a dashboard.
  4. We need flexibility with the color palettes so they can look as good (as vibrant) as we want.
  5. The method needs to support at least 1 additional palette, for example if this were used in a US election prediction map to be able to use a single third hue for a true tossup state, or as in the NYTimes post to add an additional purple sequential palette to identify precincts that voted close to 50-50.

I tried several different methods, the only method that satisfies all the requirements is the 6th one. The six methods are:

  1. A calculated field that makes blue negative and red positive and then a diverging palette based on that field. Problem: The color ramps are not symmetrical and the color legend would have to be manually created.screen-shot-2016-10-22-at-12-21-58-pm
  2. Using a custom color palette (see my post An Exploration of Custom Color Palettes) with calculated fields to normalize the values and properly assign the colors in the palette. This enables the lightness to be equivalent and dynamically updates with the data so it’s really close to the requirements and could work in some situations. Problem: This requires more effort setting up the color palettes and calculations and the color legends would have to be manually created.
  3. A dual axis map with background color and foreground as a sequential black palette with transparency. I first learned about this technique from Wil Jones of Interworks in Create a Dual Axis Color Map in Tableau. Problem: The colors are too muted.screen-shot-2016-10-22-at-12-24-17-pm
  4. Calculated field that returns discrete values and then manual assignment of colors. Problem: Colors are limited to the values that appear in the data, in this example the data is sparse so there’s no Red 6-8. so if the data changes the palette won’t automatically update (Tableau will assign the default palette) and and will need manual effort.screen-shot-2016-10-22-at-12-25-09-pm
  5. A dual axis map with calculations that are used to change the level of detail of each Marks Card by returning red states or blue states that have a geographic role assigned, then separate measures are available for each. This is getting close because it gets us completely separate palettes that can have the colors that we want. Problem: To keep the shading of each color palette the same the color ramps had to be fixed at 10%, so if the data changes the ramps will need to be edited to reflect the data or they could be misleading.screen-shot-2016-10-22-at-12-27-43-pm
  6. This starts out similar to method #5: A dual axis map with calculations that are used to change the level of detail of each Marks Card by returning red states or blue states that have a geographic role assigned, then separate measures are available for each. In addition a duplicate State dimension that is not a geography is used to increase the level of detail so the color calculations can have same ramp of values. Problem: Most complicated to set up, and can have bigger queries (which aren’t a big deal when we’re only looking at 50 states), but it’s totally dynamic and meets all the requirements.

Here’s how to build method #6:

Part 1: Dual axis maps at different levels of detail

  1. Make sure you have in your data a field that will group the states. In my case I built a map of the 50 states and grouped the states by manually selecting some and creating an ad hoc group that I called State (group). If there’s a dimension value that can slice the states this will also work, or even a calculated field that creates a dimension.
  2. Create two calculated dimensions for Red States and Blue States. Here’s the Red States formula:  IF [State (group)] = 'Red' THEN [State] END. This only returns the state value and then Null for everything else. Also note the ability to use ad hoc groups in calculations is new in version 10.0, if you’re using an earlier version of Tableau you’d need to use a calculated field or field from the data source as a dimension in the calc.
  3. Assign the Red/Blue States dimensions to the State geographic role.
  4. Create a filled map with the Red States dimension on the level of detail.
  5. Ctrl+drag (Cmd+drag on Mac) the Latitude (generated) pill on Rows to the right to create a second axis (and a third marks card, so there’s All and two Latitude (generated) cards).
  6. Activate the bottom Marks card by clicking on the the right-hand Latitude (generated) pill on Rows.
  7. On the bottom marks card replace the Red States dimension with the Blue States dimension. You should now see a view like this:

What we’ve done here is to create a dual axis map with two different levels of detail where the two Marks Cards for this map have different dimensions. The upper map has the level of detail of Red States that includes all the states in the Red group plus one Null value for all the blue states, the lower map has the level of detail of Blue States that includes all the states in the Blue group plus one Null value for all the red states.

Now at this point we could put separate % of total measures on the two Marks Cards with a Compute Using on the Red/Blue state, respectively and put those on the Color Shelf. This is method #5 before we’ve fixed the colors:


However this has two issues:

  1. Each Marks Card has a Null value (for blue states on the Red map and red states on the blue Map) that rolls up all those opposing states and ends up being the biggest % of Total and that affects the color ramp so the color range isn’t as big as it could be. Note how the largest blue value is 36.88%, but the map is entirely light blue. We can workaround this by using the filter option on the null/unknown marks indicator in the lower right, however…
  2. The two color ramps are not symmetrical at the top end in terms of lightness/darkness and the only way here to make them symmetrical is to manually fix the color ramps, which then doesn’t meet the requirement to have the view automatically update.

While can manually fix the bottom end of the color ramps at 0 to make them symmetrical there, at the top end we really need to have the % of total measures return the same values so that way we don’t have to manually fix the color ramps. That leads to the next steps in Part 2.

Part 2: Increase the level of detail without increasing the # of visible marks

I played around with a bunch of ideas on how to fix the problem including doing a linear scaling, nesting table calculations, precomputing all the values so I wouldn’t need to use table calculations, and so on, until I came back to revisiting how Tableau’s color ramp is determined for sequential palettes. The default is that bottom end of the ramp is assigned to the lowest value of the field on the Color shelf and top end of the ramp is assigned to the highest value of the field on Color. The challenge with the approach so far is that in each of the Red & Blue color ramps there’s a % of total value (the Null value for the blue states & red states, respectively) that is being calculated but is not visible. And that led to my insight: “Why not enable Tableau to have an accurate set of values for the color ramp that aren’t visible?” and a few clicks later I had it.

This solution leans on a couple of bits of somewhat esoteric Tableau knowledge:

a) In maps the visible marks are the ones that have an assigned geography. In this case for only a subset of states have an assigned geography when the Red States dimension is used on one Marks card and a different subset has an assigned geography when the Blue States dimension is used on the other Marks card. So we’ve already seen that there can be additional marks in the view that aren’t visible, therefore we can have all the states available on each marks card by adding a State dimension that isn’t drawn as a geography.

b) Table calcs have some interesting (to me, at least) behavior when there are multiple marks cards at different grains of detail the table calculation results depend on what Marks card(s) the table calculation is on. If a table calculation is on a single Marks card then only the dimensions from that Marks card plus Rows, Columns, and Pages are available for addressing and partitioning. If a table calculation is on the All Marks card then it can use any of the dimensions anywhere for addressing and partitioning, however the table calculation is separately computed for each Marks card based on the dimensions available for each Marks card i.e. the dimensions on that Marks card + Rows, Columns, and Pages, and dimensions that are not on that particular Marks card are ignored. That’s a lot of words, someday it’ll have to be another post to go into more detail.

In this case to get the % of total table calculation to the return the same result across both the red state and blue state Marks cards we need to set up the addressing of the calculation so it is across all the State dimensions that we are using – the State dimension that will get an accurate % of total plus the Red and Blue States dimensions that are used to create visible marks.

Here’s how to build it starting from Part 1 above:

  1. Duplicate the State dimension.
  2. Remove the State geographic role from the State (copy) dimension that you created in step 1 by right-clicking on the State (copy) dimension in the Dimensions window and choosing Geographic Role->None:
    screen-shot-2016-10-22-at-12-35-03-pmThis is necessary so when we add the the new State (copy) dimension to the view that Tableau doesn’t draw all the states on both the red & blue Marks cards.
  3. Click on the All Marks card header to activate it.
  4. Add the State (copy) dimension to the Detail on the All Marks card. Pro tip – you can drag it onto the Detail button or any part of the open area in the bottom of the Marks card. You should see a view that looks like this:
    Note the “27 unknown” in the lower right, that’s the sign that the level of detail has increased without making more states visible. If you see all the states in each map then you’ll need to re-do step 2.
  5. We need to build three calculations to make the colors. The first is a bog-standard % of total calculation, we can do that entirely with drag & drop. Start by dragging Sales from Measures to the Detail on the All Marks card.
  6. Right-click on the Sales pill on the Marks card and choose Quick Table Calculation->Percent of Total.
  7. Right-click on the Sales pill again and choose Edit Table Calculation… to open the Table Calculation window.
  8. Make sure Specific Dimensions is selected with all three State dimensions clicked on: Red States, Blue States, and State (copy). Validate that the table calculation is accurate by hovering over the marks, using View Data, etc.
  9. Drag the Sales pill from the Detail back to the Measures window. Tableau creates a new calculated field and gives you a chance to name it, I called it % of Total Sales.
  10. Now to build out the measures that will be used on Color. Right-click on the new % of Total Sales measure and chose Create->Calculated Field… to create a new calculated field.
  11. Set the name of the new calculated field to be Red % of Total Sales, then click Apply.
  12. Repeat step 10.
  13. Set the name of this new calculated field to be Blue % of Total Sales, then click Apply.
  14. Drag both the Red & Blue % of Total Sales measures onto the All Marks card. The All Marks card should now look like this:
  15. Activate the red state Marks card by either clicking on its header or clicking on the appropriate Latitude (generated) pill.
  16. Click to the left of the Red % of Total pill to put it on Color. The view should now look like this:
  17. Hover over the color legend and click on the drop-down menu->Edit Colors… to open the color palette.
  18. Change the palette to Tableau’s default Red sequential palette.
  19. Click on Advanced >> to open the advanced section.
  20. Click on the Start checkbox and enter 0. This is to ensure that both color palettes start from the same value.
  21. Click OK. The map should now look like this:
  22. Activate the blue state Marks Card.
  23. Click to the left of the Blue % of Total pill to put it on Color. Note that both color legends have the same ending value.
  24. Hover over the color legend and click on the drop-down menu->Edit Colors… to open the color palette.
  25. Click on Advanced >> to open the advanced section.
  26. Click on the Start checkbox and enter 0. This is to ensure that both color palettes start from the same value.
  27. Click OK. The map should now look like this:
  28. The last step in building the map is to right-click on the right-hand Latitude (generated) pill on Rows and choose Dual Axis. Here’s the map:


From here we can clean up the map formatting, edit the tooltips, turn off the unknown indicator, etc.

Notes and Next Steps

A key part of what makes the colors more accurate is that Tableau’s default red & blue color palettes use the same lightness throughout the palettes. If you are going to use a different set of palettes then take some time to make sure the lightness values are equivalent for the palettes.

If what distinguishes between the states is a measure (as opposed to a dimension like the ad hoc group used here) then you’ll need to use a variation on this technique, if you have questions post below in the comments.

If you want to get an additional discrete color (such as to mark tossup states) then you can change the measure that draws colors for a given Marks card to return a single negative value and use a diverging palette with more configuration of the Advanced >> options, for example I’ve set up Minnesota to be yellow in this  view:


If you’re careful with sequential or diverging palettes you can actually make multiple continuous ranges and/or discrete colors in the same palette, see An Exploration of Custom Color Palettes and Keith Helfrich’s post at for more details. So either method #6 or method #2 could build something like the NYTimes viz that I linked to at the beginning of this post, the question is how much mucking around with color palettes and color legends do you want to do?

Here’s a link to the Two Sequential Palettes on a Map workbook on Tableau Public.

If you think this is awesome and want more you then please check out DataBlick, we provide Tableau training and consulting and even short-term support to help you make better views and dashboards.

Marimekko wGT

What I did on my summer vacation (hint: it’s about Tableau v10 and Marimekko charts)

This summer while beta testing Tableau v10 I was very curious about the new mark sizing feature. Bora Beran did a new feature video during the beta showing a Marimekko chart aka mosaic plot. There have been a few posts on building Marimekko charts in the past by Joe Mako, Rob Austin, and a not-quite-a-Marimekko in an old Tableau KB article, but the two charts required extra data prep and the KB article wasn’t really a Marimekko, so I was really interested in what Tableau v10 could do.

I asked Bora for the workbook and he graciously sent it to me. (Thanks, Bora!) I found a problematic calculation, in particular a use of the undocumented At the Level feature that could return nonsensical results if the data was sparse. I rewrote the calculation, sent it back to Bora, and he came back asking if I’d like to write a blog post on the subject. (Thanks, Bora.) There are two lessons I learned from this: 1) the Tableau devs are happy to help users learn more about the new features, and 2) if a user helps them back they will ask for more. Caveat emptor!

Over the course of the next few weeks I did a lot of research, created a workbook with several dashboards & dozens of worksheets, arranged for Anya A’Hearn to sprinkle some of her brilliant design glitter and learned some new tricks, and wrote (and rewrote) 30+ pages (!) of documentation including screenshots. Martha Kang at Tableau made some edits and split it into 3 parts plus a bonus troubleshooting document and they’ve been posted this week, here are the links:

On Gender and Color

As part of the design process Anya and I had some conversations about how to color the marks.  The data set I used for the Marimekko tutorial is the UC Berkeley 1973 graduate admissions data that was used to counter claims of gender bias in admissions so gender is a key dimension in the data and I didn’t want to use the common blue/pink scheme for male/female. It’s a recent historical development and as a father I want my daughter to have a full range of opportunities in life including access to more than just the pale red end of the color spectrum in her clothes, tools, and toys. Anya and I shared some ideas back and forth and eventually Anya landed on using a color scheme from a Marimekko print she found online.

Screen Shot 2016-08-24 at 10.58.30 PM

Anya is going into more detail on the process in her Women in Data SF talk on Designing Effective Visualizations on Saturday 27 August from 10am-12:30pm Pacific, here’s the live presentation info and the virtual session info. It’s going to be a blast so check it out!

So that’s how I spent my summer vacation. Can’t wait for next year!


Screen Shot 2016-08-17 at 12.12.24 AM

When 576 = 567 = 528 = 456: Counting Marks

Tableau’s data densification is like…nothing else I’ve ever used. It’s a feature that is totally brilliant when it “just works” like automatically building out a running sum on sparse data and mind-taxingly complicated when a data blend’s results go haywire because densification was accidentally triggered.

What I’ve historically taught users is to always ALWAYS look at the marks count in the status bar as a first way to detect when data densification occurs. Here’s Superstore Sales data with MONTH(Order Date) on Columns, Region and State on Rows, there are 499 marks and we can see that the data is sparse by the class that are missing Abcs:

Screen Shot 2016-08-16 at 11.52.15 PM

If I add SUM(Sales) to the Level of Detail Shelf and set it to a Running Total Quick Table Calculation with the default Compute Using of Table (Across) so it’s addressing on Order Date then I see 576 marks and all the Abcs are filled in, this is Tableau’s data densification at work:

Screen Shot 2016-08-16 at 11.55.19 PM

However, here are three additional views all still using the same pill layout and Quick Table Calculations  showing three different marks counts (567, 528, and 456):

Screen Shot 2016-08-16 at 11.59.11 PMScreen Shot 2016-08-17 at 12.00.55 AM

The marks count is changing based on a variety of factors, the different quick table calculations used (running total, difference, and percent difference) are a part of it but the underlying behavior depends on whether a mark is densified or not, the pill arrangement, and whether or not a densified mark has been assigned a value (including Null) or not. Prior to Tableau version 9.0 these all would have been counted in the marks count and the views would show 576 marks for each, Tableau v9.0 changed the behavior to only count the “visible” marks.

I’ll walk through the above there examples. In this one the Running Total has been moved from the Level of Detail to the Rows Shelf and there are 567 marks.

Screen Shot 2016-08-16 at 11.59.11 PM

The reason why is that even though those combinations of Region, State, and Month have been densified for states like Iowa that don’t have any sales in the first month(s) of the year (more on how I know that below) those densified marks don’t have any assigned value (even Null) so they are not counted in the marks count nor are they counted in the Special Values indicator in the lower right.

In this view using the Difference calculation there are 528 marks and the Special Values indicator shows 48 nulls (528+48 = 576). In this case the Difference calculation is using the LOOKUP() function that is returning Null for the densified values.

Screen Shot 2016-08-16 at 11.59.11 PM

Finally in this view using the % Difference calculation there are 456 marks and the Special Values indicator shows 120 nulls (456+120 = 576). In this case the % difference calculation is spitting out extra nulls due to divide by 0 results.

Screen Shot 2016-08-16 at 11.59.11 PM

The difference is due to a change made in Tableau v9.0 where the marks count now only counts “visible” marks (Tableau’s term), where the definition of a “visible” mark is complicated, they are the “Yes” answers in the table below:

Screen Shot 2016-08-17 at 12.09.17 AM

Now one of the ways I’ve been used to checking for densification is selecting all the marks (either by Right+Clicking and choosing Select All or pressing Ctrl/Cmd+A) and then hovering over a mark and Right+Clicking and choosing View Data… or waiting for the tooltip to come up and using View Data. For example here’s the select all View Data in v9.0 for the % Difference on Rows view, the yellow cells indicate where data was densified and there are 576 rows:

Screen Shot 2016-08-17 at 12.12.24 AM

However, that doesn’t work anymore in Tableau v10.0, there was change made to the Select All functionality such that Select All only gets the “visible” marks, here’s that same view data in v10 and there are only 456 rows:

Screen Shot 2016-08-17 at 12.12.58 AM

So Select All doesn’t work the way it used to, and the marks count can change in “interesting ways” (and we haven’t gone into what things like formatting Special Values can do), so what can we do to spot densification? There are three workarounds for this, all documented in the right-most column of the table above:

  1. Select a discrete header or a range of headers, wait for the tooltip to come up, and click on the View Data icon.
  2. Right-click in the view (but not on a mark) and choose View Data…
  3. Use the Analyis->View Data… menu option.

All of these will show the densified values, here’s an animated GIF of selecting Iowa selected in the Difference on Rows view where we can see the  two Null values:

2016-08-17 00_21_03

However only one of those is actually densified, to tell that exactly we need to add a field that actually has data. In this case I’ve added SUM(Sales) to the Level of Detail Shelf and the View Data for Iowa now shows that it’s really only January that is densified, since there’s nothing at all in the January SUM(Sales) cell:

Screen Shot 2016-08-17 at 12.27.28 AM


The marks count is not a reliable indicator of the volume of densification and we need to resort to various selection mechanisms and the View Data dialog to more specifically identify how much has been densified. I’m not a fan of these changes: what I’d really like Tableau to do is to add a count of densified values to the status bar and details on what was densified to the default caption and the Worksheet->Describe Sheet… Until that time, though, hopefully this post will help you keep track of what Tableau is doing!

Here’s a link to the marks count workbook in v8.3 format (so you can open it up for yourself and see the differences in different versions).

Screen Shot 2016-07-13 at 2.29.44 PM

Getting the Version of a Tableau Workbook in a Few Clicks

In helping other Tableau users as part of DataBlick or my pro-bono contributions to the community I get a lot of Tableau workbooks in a lot of versions, in the last two weeks I’ve received v8.3, v9.0, v9.2, v9.3, and v10beta workbooks and when I edit them I need to make sure I’m using the same version of Tableau. And I’m often frustrated because I’ll open the workbook in the wrong version of Tableau and get this message:

Screen Shot 2016-07-13 at 2.30.52 PM

or this one:

Screen Shot 2016-07-13 at 2.31.34 PM

And then I have to open it up in other versions which can take awhile. I shared this problem in a Tableau Zen Master email thread and Shawn Wallwork replied with his trick for Windows that only takes a few seconds, and I was able to take that and come up with one for the Mac as well.

The basis for this technique is that the version of the Tableau workbook is stored in the XML of the .twb (Tableau Workbook) file, in particular the version attribute of the <workbook> tag, and we can open up the XML in any old text editor, I’ve highlighted the <workbook> tag that shows this workbook was created in version 9.1:

Screen Shot 2016-07-13 at 2.29.44 PM

However a .twbx (Tableau Packaged Workbook) is stored as a zip file so I’ve always just opened workbooks up in different versions of Tableau until I found the right one or once in awhile took the time to fire up a zip application, extract the TWBX to a folder and then looked at the .twb file. Both of these methods are slow. Shawn pointed out a shortcut on Windows using the free 7-Zip application that only takes a few seconds, here’s a demo:

check version windows

The steps on Windows are:

  1. Right-click on the TWBX and choose 7-Zip->Open Archive.
  2. Right-click on the .twb and choose View.
  3. Find the <workbook> tag and version attribute.
  4. Close the window.

That inspired me to figure out an equivalent on the Mac. I use BBEdit for my text editor (BareBones also offers a free text editor called TextWrangler that can do the same thing) and BBEdit natively supports zip files. It turns out I can just drag the TWBX onto the BBEdit icon on the dock and BBEdit will open up the zip file:

check version mac

The steps on Mac are:

  1. Drag the TWBX to the BBEdit or TextWrangler icon.
  2. Click on the .twb.
  3. Find the <workbook> tag and version attribute.
  4. Close the window.

This will easily save me hours over the course of a year, thanks, Shawn! There’s also a feature request for making version-checking easier that you can vote up. Also, if you’d like to change the version of the file you can edit that yourself, or (if you’re ok running the workbook XML through a web-based tool) using Jeffrey Shaffer’s Tableau File Conversion Utility.

Screen Shot 2016-07-12 at 4.32.32 PM

TRIMMEAN() in Tableau

Excel’s TRIMMEAN() function can be quite useful at removing outliers, essentially it removes the top and bottom Nth percent of values and then computes the mean of the rest. Here’s the equivalent formula in Tableau that in Superstore Sales computes the TRIMMEAN() of sales at the customer level removing the top and bottom 5th percentile of customers when used with the AVG() aggregation:

{FIXED [Customer Name]: SUM(
    IF {FIXED [Customer Name] : SUM([Sales])} < 
    {FIXED : PERCENTILE({FIXED [Customer Name] : SUM([Sales])}, .95)}
    AND {FIXED [Customer Name] : SUM([Sales])} > 
    {FIXED : PERCENTILE({FIXED [Customer Name] : SUM([Sales])}, .05)} THEN

Read on for how to build and validate your own TRIMMEAN() equivalent in Tableau.

When building out calculations in Tableau I try to let Tableau do as much of the computation as possible for both the calculations and the validation, so I’m typing as little as I can. Starting with Superstore, let’s identify the top and bottom 5th percentiles, here’s a view using a reference distribution:

Screen Shot 2016-07-12 at 3.57.38 PM

Now we know what we’re going to have to remove. The next step is to duplicate this worksheet as a crosstab, then build out calcs that can return the 5th and 95th percentiles of Sales at the Customer Name level. While this can be done with table calculations (here’s an example from the Tableau forums) I’m going to use FIXED Level of Detail Expressions so I’ve got a dimension I can use, so for example I could compare the trimmed group to the non-trimmed group. Here’s the 95th percentile Level of Detail Expression:

{FIXED : PERCENTILE({FIXED [Customer Name] : SUM([Sales])}, .95)}

The inner LOD is calculating the sales at the Customer level, then the outer LOD is returning the 95th percentile as a record level value. Here’s the two calcs which have values that compare to the reference lines above:

Screen Shot 2016-07-12 at 4.03.46 PM

The next step is to filter out the values outside of the desired range, here’s the TRIMMEAN Filter formula:

{FIXED [Customer Name] : SUM([Sales])} < 
    {FIXED : PERCENTILE({FIXED [Customer Name] : SUM([Sales])}, .95)}
AND {FIXED [Customer Name] : SUM([Sales])} > 
    {FIXED : PERCENTILE({FIXED [Customer Name] : SUM([Sales])}, .05)}

This uses the 5th and 95th percentile formulas and only returns True when the Customer level sales is less than the 95th percentile or greater than the 5th percentile, we can visually validate it by dropping it on the Color Shelf:

Screen Shot 2016-07-12 at 4.06.37 PM

Now that we have this the next step is to calculate what the trimmed mean would be. Again, we can use a view with a reference line, this time it’s been filtered using the TRIMMEAN Filter calc and the reference line is an average:

Screen Shot 2016-07-12 at 4.08.16 PM

Now we can embed the TRIMMEAN Filter formula inside an IF/THEN statement to only return the sales for the filtered values, this is the Trimmed Sales calc:

IF {FIXED [Customer Name] : SUM([Sales])} < 
   {FIXED : PERCENTILE({FIXED [Customer Name] : SUM([Sales])}, .95)}
AND {FIXED [Customer Name] : SUM([Sales])} > 
   {FIXED : PERCENTILE({FIXED [Customer Name] : SUM([Sales])}, .05)} THEN

And here it is in the workout view, only returning the sales for the trimmed customers:

Screen Shot 2016-07-12 at 4.15.36 PM

Now that we have the trimmed sales there are two ways we can go. If we want the trimmed mean without the Customer Name in the Level of Detail then we can validate that in our workout view by using Tableau’s two-pass Grand Totals to get the average of the customer-level trimmed sales. This was created by:

  1. Removing the TRIMMEAN Filter pill from Colors (this increases the vizLOD and is no longer necessary).
  2. Clicking on the Analytics tab.
  3. Dragging out a Column Grand Total.
  4. Right-clicking the SUM(Trimmed Sales) pill on Measure Values and setting Total Using->Average.

Scrolling down to the bottom we can see that the overall trimmed mean matches of 2,600.79 matches the one from the reference line.

Screen Shot 2016-07-12 at 4.20.33 PM

Note that we could have used the Summary Card instead, however using the Grand Total lets us see exact values.

There’s a problem, though, if we use the Trimmed Sales all on its own in a view it breaks, whether using SUM() or AVG():

Screen Shot 2016-07-12 at 4.25.49 PM

The reason why is that the Trimmed Sales is a record level value and Superstore is at the level of detail of individual order items, but we’re trying to compute the trimmed mean across Customer Names. For the true trimmed mean in this case we need to aggregate this trimmed sales to the Customer Name like we did in the workout view, here’s the Trimmed Sales (Customer Level) formula that uses the Trimmed Sales and wraps that in an LOD to get the Customer Level sales:

{FIXED [Customer Name]: SUM(
    IF {FIXED [Customer Name] : SUM([Sales])} < 
    {FIXED : PERCENTILE({FIXED [Customer Name] : SUM([Sales])}, .95)}
    AND {FIXED [Customer Name] : SUM([Sales])} > 
    {FIXED : PERCENTILE({FIXED [Customer Name] : SUM([Sales])}, .05)} THEN

This returns the same results in the workout view:

Screen Shot 2016-07-12 at 4.31.49 PM

And works all on its own in a view:

Screen Shot 2016-07-12 at 4.32.32 PM

Now this is a case where the FIXED level of detail expression is returning different results depending on the level of detail of the view, if we want it to return the same result then we can wrap all that in one more LOD expression, this is the TRIMMEAN Fixed calculation:

    {FIXED [Customer Name]: SUM(
        IF {FIXED [Customer Name] : SUM([Sales])} < 
        {FIXED : PERCENTILE({FIXED [Customer Name] : SUM([Sales])}, .95)}
        AND {FIXED [Customer Name] : SUM([Sales])} > 
        {FIXED : PERCENTILE({FIXED [Customer Name] : SUM([Sales])}, .05)} THEN

And here it is in the workout view and a view without any dimensions:

Screen Shot 2016-07-12 at 4.34.36 PM

Screen Shot 2016-07-12 at 4.35.35 PM



Final Comments

This is a good (and bad) example of how Tableau is different from Excel. In one bad sense note that I didn’t parameterize the percentage for the trimmed mean, this is because in Tableau it would require two parameters because we can’t put calculations as the arguments to the PERCENTILE() function. In another bad sense the calculation requires understanding Level of Detail expressions and is not wrapped into a simple formula. On the other hand we’ve got very precise control over what the calculation is computing over with those Level of Detail expressions and aren’t just limited to doing trimmed means, we could do trimmed medians, get the Nth percentile of the trimmed values, etc.

Here’s the trimmed mean workbook on Tableau Public.

Screen Shot 2016-07-08 at 4.02.54 PM

Parallel Coordinates via Pivot and LOD Expressions

Parallel coordinates are a useful chart type for comparing a number of variables at once across a dimension. They aren’t a native chart type in Tableau, but have been built at different times, here’s one by Joe Mako that I use in this post for the data and basic chart. The data is a set of vehicle attributes from the 1970s, I first saw it used in this post from Robert Kosara. This post updates the method Joe used with two enhancements that make the parallel coordinates plot easier to create and more extensible, namely pivot and Level of Detail Expressions.

The major challenge in creating a parallel coordinates chart is getting all the ranges of data for each variable into a common scale. The easiest away to do this is to linearly scale each measure to a range from 0-1, the equation is of the form (x – min(x))/(max(x)-min(x)). Once that scale is made then laying out the viz only needs 4 pills to get the initial chart:

Screen Shot 2016-07-08 at 3.25.20 PM

The Category is a dimension holding the different variables, ID identifies the different cars in this case, the Value Scaled is the scaled measure that draws the axis. Value Scaled is hidden in the tooltips while Value is used in the tooltips.

Where this is easier to create is using Tableau’s pivot function, in Joe’s original version the data is in a “wide” format like this:

Screen Shot 2016-07-08 at 3.20.33 PMSo for each of the measures a calculation had to be built, and then the view was built using Measure Names and Measure Values:

Screen Shot 2016-07-08 at 3.47.05 PM

The major limitation here is in the tooltips (in fact, Joe had rightly hidden them in the original, they were so useless):

Screen Shot 2016-07-08 at 3.33.36 PM

The tooltip is showing the scaled value, not the actual value of acceleration. This is a limitation of Tableau’s Measure Names/Measure Values pills…If I put the other measures on the tooltip then I see all of them for every measure and it’s harder to identify the one I’m looking at. Plus axis ranges are harder to describe.

Pivoting Makes A Dimension

I think of Tableau’s Measure Names as a form of pivoting the data, to create a faux dimension. I write faux because beyond the limits mentioned above we can’t group Measure Names, we can’d blend on Measure Names, we can’t do cascading filters on Measure Names, etc. The workaround is to pivot our data so we turn those columns of measures into rows and get an actual “Pivot field names” dimension (renamed to Category in my case) and a single “Pivot field values” measure (renamed to Value in my case):

Screen Shot 2016-07-08 at 3.45.22 PM

Then for the scaling we can use a single calculation (instead of one for every original column), here’s the Value Scaled measure’s formula:

([Value] - {EXCLUDE [ID] : MIN([Value])})/
({EXCLUDE [ID] : MAX([Value])} - {EXCLUDE [ID] : MIN([Value])})

I used an EXCLUDE Level of Detail Expression here rather than a TOTAL() table calculation as an example of how we can use LODs to replace table calculations and have a simpler view because we don’t have to set the compute using of the table calculation.

Now with a real Category dimension in the view the Value Scaled calc is computed for each Category & ID, and this also means that if we put the Value measure in the view then that is computed for each Category & ID as well, immediately leading to more usable tooltips:

Screen Shot 2016-07-08 at 3.55.53 PM

For a quick interactive analysis this view takes just a couple of minutes to set up and the insights can be well worth the effort. Prior to the existence of Pivot and LOD expressions this view would have taken several times as long to create, so for me this revised method takes this chart type from “do I want to?” to “why not??”

Cleaning Up

To put this on a dashboard some further cleanup and additions are necessary. Identifying the axis ranges is something that is easier as well with the pivoted data. In this case I used a table calculation to identify the bottom and top-most marks in each axis and used that as mark labels to identify the axis range:

Screen Shot 2016-07-08 at 3.58.04 PM

The Value for Label calculation has the formula:


The addressing is an advanced Compute Using so that it identifies the very first or last mark in each Category based on the value:

Screen Shot 2016-07-08 at 4.01.00 PM

In addition I created two different versions of the value pill that each had different number formatting and used those on the tooltips, used Joe’s original parameters for setting the color and sort order with revised calculations (which were also easier to use since Category is a dimension), and finally added a couple of other worksheets to be the target of a Filter Action to show details of the vehicle:
Screen Shot 2016-07-08 at 4.02.54 PM

Click on the image above to download the workbook from Tableau Public.

Screen Shot 2016-06-30 at 2.36.54 PM

Sorting a Dimension by Two Values At Once

I recently got a question via email about how to sort a view by two different criteria at the same time. Here’s an Excel table of opportunities where for each month and Opportunity ID there’s a forecast of size of opportunity:

Screen Shot 2016-06-30 at 2.12.45 PM

The goal here is to sort the opportunities within each account type by the nearest (by month) and biggest opportunities (by Forecast size) first, so it looks more like this in Tableau:

Screen Shot 2016-06-30 at 2.13.38 PM

Now with that data in Excel arranged in a tabular form I can sort on December, then November, then October, and so on, and get the desired sort:

Screen Shot 2016-06-30 at 2.14.29 PM

But in Tableau I can’t do that, if I try to work with the data in that “wide” format with a column for each month it just won’t work. If I use Tableau’s pivot feature to make the data “tall” with a record for each Opportunity ID and Month then I still run into problems. I want to sort the opportunities by each month and by Forecast but when I try to sort the Opportunity ID dimension I can’t get it to work, it only sorts by a single month’s values, so in the view below Opportunity ID 864280 should be the first new one for August since:

Screen Shot 2016-06-30 at 2.15.31 PM

The Excel way isn’t good because each month I have to manually re-sort the data. And in Tableau it just seems impossible to get the right sort because it looks like we need to sort in two different directions at once (get the earliest non-zero month for each opportunity, and then sort up and down the opportunities in each account type), and Tableau only lets us sort on one thing at a time. However, it is possible – read on for how to get this kind of sort in Tableau and maybe learn a few math tricks!

Part of how this kind of problem can be more challenging is the way the problem (and data) is initially presented to us. When we see the data in the crosstab form in Tableau the *appearance* is that we need to sort in two different directions. In fact, we really only need to sort in one direction based on the forecast value in the first month for each opportunity, so in the view below we’d want Opportunity ID 864271 to be the first one sorted because it’s from July 2016.

Screen Shot 2016-06-30 at 2.16.45 PM

Each opportunity row in the view needs to be sorted within the account type by the first (earliest) month where there is a non-zero forecast and then by the value of Forecast in descending order for that month.

The key to sorting headers and panes in Tableau is that it’s done using the discrete (blue) pills on Rows or Columns from left to right. So the left-most discrete (blue) pill headers are sorted, then the 2nd discrete pill’s headers are sorted, and so on. For discrete dimensions from a primary source we can sort by a measure, use the default alphanumeric sort, or a manually, otherwise any discrete pills are by default alphanumerically sorted or manually sorted.

Therefore in this case I knew I needed to either return a measure that could sort some dimension (like the Opportunity ID) or return a discrete dimension value that with the default alphanumerical sort would work right. Note that filtering wouldn’t work here because the goal is to show a sorted crosstab.

The next part of working out the solution is how to structure this value for sorting. I’ve done some multi-level sorting in the past where I needed a nested sort of single dimension by two different criteria, and a common construct is a number of the form X.Y where the integer portion X is from one criteria and the decimal portion N is from the other criteria. So with the default alphanumerical sort 1.2 comes before 1.3 comes before 2.1 etc.

So for the integer part of the sort I need to convert the date for each opportunity into a number where the Forecast is greater than 0. The Date Number (temp) calc has the formula:

IF [Forecast] > 0 THEN

Screen Shot 2016-06-30 at 2.17.34 PM

This convers the date into an integer, in this case the number of days since 1/1/1900. To get the first (earliest) month for each opportunity then all I need to do is aggregate it with MIN() at the level of Opportunity ID:

Screen Shot 2016-06-30 at 2.18.01 PM

Ultimately, this is is what we’re going to do to get that pill sort of Opportunity ID’s in the final view.

For the decimal part of the sort I needed a number where the smallest numbers reflected the largest values, and it needed a value between 0 and 0.999999 (it can’t be a whole value of 1 because that would affect the integer sort). A way to turn a set of positive numbers into decimal numbers between 0 and 1 is to do X/(max X). In this case X is the Forecast, so to get the max X in the data I used the Level of Detail Expression, here’s the Max Forecast (temp) formula:

{FIXED : MAX([Forecast])}

Screen Shot 2016-06-30 at 2.18.35 PM

Now if I do [Forecast]/MAX([Forecast]) that’s going to return a number between 0 and 1 that preserves the original ordering of values, i.e. bigger values of Forecast are closer to 1. So to invert that I used use 1 – X/(max X). So if (max X) is 10 and X is 9, then the result of (1 – 9/10) is 0.1, while if X is 2 then the result of (1- 2/10) is 0.8, a bigger number.

We avoid results of 1 that could affect the sort by skipping values of where the Forecast is 0, here’s the Invert Forecast (temp) formula:

IF [Forecast] > 0 THEN
    1-[Forecast]/[LOD Max Forecast Value]

Screen Shot 2016-06-30 at 2.19.19 PM

I could have avoided the LOD expression for the max value by just setting a gigantically huge number, however past experience with foreign currencies has shown me that whatever huge number I can imagine is likely to be smaller than reality so I chose to make sure that the value is coming from the data

With all the values worked out I could now put everything together into a single calculation, this is the Sort calc that returns record-level values:

IF [Forecast] > 0 THEN
    + (1-[Forecast]/{FIXED : MAX([Forecast])})
//Forecast is 0, return a really big number that will be put at the end of the sort

Screen Shot 2016-06-30 at 2.20.09 PM

This calc returns the numbers as record level values.

To show how the sort works out I set up this view where the Sort calc is used as the leftmost discrete dimension to show what gets sorted first, with the bar chart we can quickly visually verify that the dates are sorted with earliest months first and then by the Forecast within each month:

Screen Shot 2016-06-30 at 2.20.41 PM

Note that there’s a different value for each Opportunity ID/month combination, when what we really want is that single minimum value for each Opportunity ID/month. So we need to aggregate the Sort measure with MIN() at the level of detail of Opportunity ID, and we can do just that using a pill sort on the Opportunity ID dimension:

Screen Shot 2016-06-30 at 2.21.14 PM

Now we can arrange pills to build the original crosstab view and have the desired sort:

Screen Shot 2016-06-30 at 2.13.38 PM

And as the data updates the sort will automatically work, in this case I’ve added January 2017 to the data:

Screen Shot 2016-06-30 at 2.22.37 PM


The following bits of Tableau knowledge were necessary to get the desired results:

  • How Tableau sorts views using discrete pills.
  • How Tableau’s pill sorts work.
  • A tiny bit of Level of Detail Expression building.

And then the following math tricks were used:

  • Combining two different numbers into one using an integer number for one value and a decimal number for the second value.
  • Making positive numbers into a range of 0-1 using X/(max X). A different formula would be needed if there were negative numbers and/or the desired range was different.
  • Inverting ranges to make big numbers small and small numbers big using 1 – X/(max X)

FYI if LOD expressions are not available in your particular data source then you could use a table calculation, a data blend, or just manually enter your equivalent of the Max Forecast value. I set up a table calculation version as well in the Sorting by Two Values Tableau Public workbook.