3 Replies Latest reply on Nov 10, 2014 2:58 PM by Jonathan Drummey

    How do I format a dimension by using a measure?

    joe major

      I'm using a Tableau standard work book  for this idea and would like to create a calculated column that does this:

       

      If a [Product-subcategory] = Regular Air then "red", meaning that the 534 is red AND the word APPLIANCES is red.

       

      Or it could be: if the sub category applsales is regular air, then red (but the word APPLIANCES) should be red too.  The line below it needs to stay black.

       

      Any ideas?

       

      Capture.PNG

        • 1. Re: How do I format a dimension by using a measure?
          Jonathan Drummey

          Hi Joe,

           

          There is no built-in functionality for dynamically coloring those headers, our only static options are setting the background color and choosing banding colors.

           

          There are three hacks that people have used:

           

          1) Use two separate worksheets, where the headers are actually a separate highlight table using hidden discrete pills to get the layout. This has the problem that if your data table would be taller than the display, you end up with two separate scrollbars (one for each worksheet).

           

          2) Use a multiple axis crosstab to create the headers and the detail (see http://drawingwithnumbers.artisart.org/older-but-still-useful-conditional-formatting/ for more info), using hidden discrete pills to get the layout. This is more difficult to set up, has fixed column widths for all columns (headers and detail), and can be very very slow because of the extra hoops it makes Tableau jump through.

           

          3) Use a separate scaffold data source that has the dimensionality needed to drive the display (and potentially filtering), a Tableau data blend of your original data against that source, calculated fields where necessary to get the right values, and a highlight table with one or more pills on the Text Shelf to return the text and numeric values that can then be colored either by using the Color Shelf or by using text formatting for different measures. This can still run into the problem of column widths being fixed, but with the scaffold source and calculated fields you can end up having more precision over where things go (but still not necessarily ideal).

           

          The thing is, when someone asks for these kinds of layouts they are usually trying to duplicate some sort of Excel-based conditional formatting worksheet. Tableau is not currently designed to do that kind of thing, and the effort spent to shoehorn something like this into Tableau often results in various undesired results. See http://www.theinformationlab.co.uk/2013/08/27/how-not-to-use-tableau/ for some commentary. I *strongly* recommend that you take some time to ask two questions:

           

          - What do your users want to find out from this view?

          - What will they do once they get that information?

           

          And ask further questions as necessary so you can really understand what they are looking for. Then create a view/dashboard that a) more directly gives your users what they are looking for and b) is easier to build and maintain in Tableau. For example, if they want to flag outliers on a certain measure, then why not sort the view so those outliers are at the very top? Or even just filter for those outliers? If the coloring is based on some sort of comparison, then you can use circle/square/shape plots, slope graphs, etc. to show that visually. Even a simple line chart set up as a sparkline with a dual-axis to get a colored shape mark can communicate a trend and a KPI much more effectively than a cell where 90% of the ink is for a background color or coloring an entire category label.

           

          Jonathan

          • 3. Re: How do I format a dimension by using a measure?
            Jonathan Drummey

            I’m glad it was useful for you!