5 Replies Latest reply on Jan 16, 2014 6:35 AM by Jonathan Drummey

    Dual Axis with two measured values on each Y axis

    Jay Fagley

      I am trying to do a KPI measurement. The left hand axis has values in the tens of thousands and the right hand axis has values in the hundreds.

       

      I have two data series that I would like to display on the left hand axis and two data series that I would like to display on the right hand axis.

       

       

      I have figured out how to display the 2 data series on the left axis and 1 data series on the right axis, however I cannot figure out how to get the second data series on the right hand axis.

       

       

      Thanks,

      Jay

      using Tableu 8.0

        • 1. Re: Dual Axis with two measured values on each Y axis
          John Sobczak

          I would be interested if there is a creative method for this as well.  The reason you can do 2 on one axis is because of the use of "measure names" on that axis. And you can only have one association of "measure names" in a worksheet, thus the challenge of not being able to use it for the 2nd axis.

          • 2. Re: Dual Axis with two measured values on each Y axis
            Jonathan Drummey

            You'll need to reshape the data to be "taller" so that at least two measures have different dimension values, then you can build one axis using Measure Names/Values, and the other part of the dual axis using the chosen dimension on the Color Shelf or some other Shelf with a single measure.

             

            For example, if you have M1, M2, M3, and M4 as columns in your data, you can reshape the data so that you have columns M1, M2, MDimension, and MValue, where MDimension has values M3 and M4, and Value has the appropriate values.

             

            There's an Idea to allow multiple instances of Measure Values in the view at http://community.tableau.com/ideas/1087. If you want to look more at how data needs to be reshaped, you can see some of the examples in http://drawingwithnumbers.artisart.org/bars-and-lines/.

            • 3. Re: Re: Dual Axis with two measured values on each Y axis
              Jim Wahl

              A scaffold might be another approach. Maybe easier, maybe not, depending on your data and the view.

               

              To create the scaffold, you need a row in Excel for every combination of measure and dimension you want in the view.

               

              Here's a quick, simple example where with four measures---Sales, Profit, Profit Ratio, Shipping Cost Ratio---and a Year dimension.

               

              Create the Scaffold

              To create the combination of rows, I used Tableau's Excel reshape tool. Starting with this:

              2014-01-15_23-16-32.png

               

              Reshape to this (and remove the column of 1s):

              2014-01-15_23-24-49.png

               

              Using the Scaffold in Tableau

              Next connect to this Excel file in Tableau and create to calculated fields in the data source for the measures (from the secondary data source) you want on each axis:

              Values LeftAxis =

              // Note the ATTR(), which is necessary, because the in the secondary data source are required to be aggregates.
              // Tableau aggregates data in the secondary data source before blending it with the primary data source. 
              // Rather than ATTR() we could have used MIN() or MAX(), which are identical becuase there is only one value per row. 
              CASE ATTR([Measure])  
              WHEN "Profit" THEN SUM([Sample - Superstore Subset (Excel)].[Profit])
              WHEN "Sales" THEN SUM([Sample - Superstore Subset (Excel)].[Sales])
              END
              

               

              Duplicate this and edit for Values RightAxis.

               

              Finally, you need a linking field to the main data source. I created a Year field in the Sample - Superstore data set.

               

              Now you can drop these on the Measure field (from the scaffold data source) on the Color button, and drag the Values

              2014-01-15_23-30-37.png

               

              The downside to this approach is that you need to determine what dimensions you want in the view for filtering. For example, if you wanted to filter by Category, the scaffold would like the below (you can build this with iterative reshape steps in Excel using the Tableau Reshape Tool):

              2014-01-15_23-40-30.png

              Jim

              2 of 2 people found this helpful
              • 4. Re: Dual Axis with two measured values on each Y axis
                John Sobczak

                Wow, both these ideas are really clever.  I know I will have use for this and I am definitely booking this in my archives for future reference!