4 Replies Latest reply on Dec 6, 2018 6:20 PM by kate summer

    How to combine multiple measures into one dimension to join?

    kate summer

      Hi,

      I want to create a table comparing daily metrics to yearly target as shown in the expected output sheet. My daily data source is custom SQL. Discount, Profit, profit ratio, quantity, sales are measure fields. In my real data, they are all calculated measure fields. My yearly target table has Discount, Profit, profit ratio, quantity, sales combined as one dimension called label. How to join the daily data and the yearly target table to create a table in the expected output sheet? A sample workbook is attached.

       

      Thank you,

      Kate

        • 1. Re: How to combine multiple measures into one dimension to join?
          swaroop.gantela

          Kate,

           

          Were it not for the profit ratio calculated field,

          you could pivot the fields of interest and join

          on Pivot Field Name=Label.

           

          To include calculated fields, I made an attempt using Tableau Prep to:

          - make all the daily aggregations

          - calculate the Profit Ratio

          - Pivot all the desired columns

          - join with the Targets

           

          In both cases, there is some unsatifactoriness with the formatting

          of the Pivot Value. But this can be addressed with a calculated field

          and some string manipulation.

           

          Please see workbook v10.2 and tflx attached in the Forum Thread

          How to combine multiple measures into one dimension to join?

          1 of 1 people found this helpful
          • 2. Re: How to combine multiple measures into one dimension to join?
            kate summer

            Hi Swaroop,

             

            Thank you very much for your effort. I learned to pivot in Tableau Prep from you. I was thinking pivot the target table instead because of possible performance issues. The target table has much fewer rows of data and it only gets updated once a year. There are new rows coming in the daily tables every day. So how about pivoting the target table and join it with the daily table? I didn't think this through...

             

            Thank you,

            Kate

            • 3. Re: How to combine multiple measures into one dimension to join?
              swaroop.gantela

              Kate,

               

              I fear that pivoting only the targets will be problematic

              because the data side will still need to use MeasureNames|MeasureValues

              to achieve the desired table format, but [MeasureNames] cannot be called on

              in calculated fields or joins.

               

              Some other ideas, each with their own drawbacks, could be:

              1. Join the targets to the data on Year

              Then every data row will have the targets and it will be a simple subtraction

              to calculate the difference.

              However, it does not appears to me that the output data

              can only be arranged as a single column,

              not in the table format you desire.

               

              2. Union the targets to the data

              This generates the new [Table Name] which can be used in calculations,

              but I think it will again be difficult to get the results in the table format you need.

              Also may be difficult to incorporate goals for calculated fields (one metric / another metric).

               

               

              If the results were to be displayed graphically instead of in a table,

              there may be easier ways to achieve this.

               

               

              I feel like I'm missing an easier way to accomplish this.

              Certainly open to other suggestions from the Community.

               

              Please see workbook v10.2 and revised datasource attached in the Forum Thread.

              1 of 1 people found this helpful
              • 4. Re: How to combine multiple measures into one dimension to join?
                kate summer

                Hi Swaroop,

                 

                I think the union method is promising. I will try that.

                 

                Thank you,

                Kate