3 Replies Latest reply on Sep 25, 2018 7:46 AM by Joe Oppelt

    Apply a filter to some measures in a table, but not all

    Joe Geglio

      I'm trying to create a table that shows values for three distinct years (academic terms), with additional columns showing the % difference from the most recent year to each of the preceding two years. In the attached workbook, I have a dashboard called 'Want this' showing what this would ideally look like - with three columns for each year, and an additional two columns showing the % difference, color coded for positive and negative change. The dashboard in the attached shows this in two separate tables; what I'm trying to do is create this layout in a single table.


      The next tab, 'measures', is the closest I've gotten to getting what I want. This table shows each term as its own separate measure, alongside the % difference measures. I've run into two issues with this one. First, I can't figure out how to attach a color filter to just the % difference measure (one filter per field) so that none of the other measures are affected by the filter. Second, I'd like to add the 'snapshot date' field as a tooltip for each value in the Term columns (see the 'Want this' dashboard), but the inclusion of the % difference columns is causing this date to appear as a *.


      Alternatively, I have the year (term) as a dimension (dimension tab), in which I am able to utilize the date tooltip, but now I can't figure out how to include the % difference measures. I've experimented with table calculations and window functions, but I don't have much experience in using either. Thanks for your help!

        • 1. Re: Apply a filter to some measures in a table, but not all
          Joe Oppelt

          you're not going to create this in one sheet like that.  Not easily, anyway.  Your stab in lowercase-measures sheet is a start toward doing it that way, but you'll have to abandon the MeasureNames/MeasureValues construct.


          On my copy of the measures sheet I switched to a technique whereby each measure is its own column.  I use SUM(0) as a bogus axis, and then I plunked SUM(Current Term) on labels.  (I did the same for Current Term-2.  You'll do the same for each measure.)  You can use different values in the bogus measures.  I just go with SUM(0) as my own preference.


          The Current column is what it looks like in raw format. I have formatted [Current Term-2] as follows:


          I selected white as the color for the bars that tableau creates for the zero value.  This hides the blue mark from user view.


          I centered the label so that your values show up in the center of the column.  (You can do all sorts for formatting things here, but that will be a matter of user preference.)


          I right-clicked on the axis and did edit axis.  I changed the label from "0" to "Current Term".  then I clicked the Tick Marks tab and set the ticks to NONE.  This eliminates the axis values on the ticks.


          I clicked in one of the numbers in the column and chose FORMAT.  Then select the 5th icon at the top of edit format to change line settings.  Set Zero Line to None.  that takes the faint zero line off the chart.


          Next, to set individual colors.  You can't just make a red-green calc.  That will govern the color of the underlying bar, not the text.  (I made a calc called 900? to demonstrate this.  On the first SUM(0) mark, drag that to the colors shelf.  You'll just see those zero bars get set.)  So here is what you need to do.


          Create two calcs.  I did [Current Term -2 900] and [Current Term -2 less than 900].  Red if less than 900, and italicized.  Green if greater than 900, and bold.  Look at the two calcs.  They are mutually exclusive.  For each mark, only one will be set, and the other null.  I put both on LABELS, and edited the labels editor to select colors and formats and I placed them side-by-side.  Only one will display.  It works as you see in the measures(3) sheet.


          You'll have to do this for each measure.  It's tedious, but it's a way to make Tableau behave in an excel-like manner.  (You'll see all over this board that Tableau is not excel.  It doesn't have a cell-oriented structure.  It's dimension-oriented.  So to make excel in tableau can be complicated.)


          See attached.

          1 of 1 people found this helpful
          • 2. Re: Apply a filter to some measures in a table, but not all
            Joe Geglio

            Thanks Joe. I would have assumed there was a much simpler solution to this, and I appreciate you detailing the workaround. Establishing the dummy x-axis really opened things up for me. I successfully re-engineered this table per your instructions and applied a dynamic color filter to the % difference columns. I cleaned things up a bit by adding dual axis for each column to put the headers on top. Attached is my finished table.


            Thanks again!


            • 3. Re: Apply a filter to some measures in a table, but not all
              Joe Oppelt

              Joe Geglio wrote:


              ... I cleaned things up a bit by adding dual axis for each column to put the headers on top. ...

              Yup. That's often the next question when someone sees this technique.  And dual axis is the way to address it.


              It's not easy.  Well it's not difficult once you get it.  It's just tedious and awkward.  (And I hope your user doesn't ever try to download a crosstab of this sheet!  All those zeros will litter the download.)  But it also gives you the capability to mix text and shapes and bars all in one chart.