4 Replies Latest reply on Apr 20, 2018 4:10 AM by Amit Naik

    How can I show 3 Measures (2 on dual axis 1 separate) for 1 Month ?

    Amit Naik

      Hello Gurus

      I want something like in the diagram below. Pls suggest how to do in Tableau OR how I can format the data to get the needed visual ?

      I have attached sample Excel and twbx.

       

      I am still trying on this also  know how to show 3 measures (depicted on tab 2 of attached twbx) but how to get this ?

       

      Expected Output

       

       

      Current State Tab

        • 1. Re: How can I show 3 Measures (2 on dual axis 1 separate) for 1 Month ?
          Mark Holtz

          In the case of a display like this, which I would call an "inventory" of sorts, I would recommend a visual closer to your "current state" than what you've identified as your target.

           

          AREA chart for the "beginning balance" or "ending balance" for the period in question (I prefer ending balance, but you appear to have the [Members at Start of Period] as your "inventory" figure)

          Positive Bars for additions

          Negative Bards for subtractions/removals.

           

          But, if you want to, you could create the visual you had in your screenshot too.

           

          The key is using Measure Names on the Columns shelf.

          However, because your data is structured as a single record per period, you are using different Measures for "Additions" and "Subtractions."

           

          The key to setting up your data to lend itself to your desired visual is that you want to UNPIVOT your source data so that you have a single column with an amount and then a "description" field for the metric type.

          This will reduce the columns and increase the rows in your data, but grants you greater flexibility in Tableau.

           

          Forgive my crappy screenshot--I don't have Excel installed on this computer, so I had to use Google Sheets.

          Hopefully that gives you the idea.

           

          With your data shaped that way, you can now have greater flexibility.

           

          I'd create an additional calculated field as

          "Inventory" which basically "undoes" the unpivot and pivots the inventory figures back out into their own field:

          IF [Metric Type] = 'Inventory' THEN [Count At Start] ELSE NULL END

           

           

          Then create another Calculated Field (since your raw data doesn't handle the sign of the "Deleted" items--I'll call it "Added/Deleted":

          IF [Metric Type] = 'Added' THEN [Count At Start]

          ELSEIF [Metric Type] = 'Deleted' THEN -1*[Count At Start]

          END

           

          Then, you can Measure Values on the Rows Shelf and Measure Names on the columns shelf and get 2 "columns" where the first will be Inventory and the second will be the signed [Added/Deleted].

          You can color by 'Metric Type' to get 3 distinct colors.

          1 of 1 people found this helpful
          • 2. Re: How can I show 3 Measures (2 on dual axis 1 separate) for 1 Month ?
            swaroop.gantela

            Amit,


            I agree with Mark regarding placing all the measures in a single column.

            I did this with the Pivot function on the Data Source Tab.

            As Mark described, this allows for calculations to be done using the Pivot Field Name,

            so you could set a position 0 for the Start Value and Position 1 for the other values.

             

            Please see workbook attached in the Forum Thread.

             

            268065three.png

            1 of 1 people found this helpful
            • 3. Re: How can I show 3 Measures (2 on dual axis 1 separate) for 1 Month ?
              Hari Ankem

              Here is another solution.

              Screen Grab.png

               

              A 10.5 workbook is attached. Hope it helps.

              1 of 1 people found this helpful
              • 4. Re: How can I show 3 Measures (2 on dual axis 1 separate) for 1 Month ?
                Amit Naik

                Thanks Mark Holtz & @swaroop

                combining 2 measures Added/Deleted did it. Also it must be noted that the Months need to be discrete.