3 Replies Latest reply on May 11, 2018 2:50 PM by Okechukwu Ossai

    How to look up dimension and return measure in tableau

    Mey Chau

      Hi, I have attached an excel workbook for an example.

       

      I was hoping someone would be able help me figure out how I can create a calculated field between two data sets to make the dynamic.

       

      1st data set is:

       

      ProductComponent AComponent A %Component BComponent B %Component CComponent C %
      1Vanilla40%Milk60%None0%
      2Milk50%Sugar40%Nuts10%
      3Sugar100%None0%None0%
      4Nuts100%None0%None0%
      5Chocolate65%Vanilla35%None0%

       

       

      2nd data set

       

       

      ComponentPrice
      Vanilla $                      2.50
      Milk $                      1.25
      Sugar $                      3.25
      Nuts $                      2.00
      Chocolate $                      1.75

       

      Output needed:

      Math:  1st data set (Product 1 Comp A) * 2nd data set (Price for Component) +1st data set (Product 1 Comp b) * 2nd data set (Price for Component) +1st data set (Product 1 Comp b) * 2nd data set (Price for Component)

       

      ProductOUTPUT Needed
      1 $                      1.75
      2 $                      2.13
      3 $                      3.25
      4 $                      2.00
      5 $                      2.01

       

      Any help would be great! Thank you!!

        • 1. Re: How to look up dimension and return measure in tableau
          Zhouyi Zhang

          Hi, Mey

           

          Please find my solution attached as well as below screenshot.

           

           

          Hope this helps

           

          ZZ

          • 2. Re: How to look up dimension and return measure in tableau
            Mey Chau

            Hi, thank you for the reply.

             

            I should have clarified the data type.

             

            Data set 1 is an excel file

            Data set 2 is Odata

             

            Therefore I do not think you can join, but if you can would you be able to advise?


            Thank you!

            • 3. Re: How to look up dimension and return measure in tableau
              Okechukwu Ossai

              Hi Mey,

               

              OData seems to be an extract only data source. Data blending is another option if you can't join.

               

              Your data is not well structured. This solution uses a combination of pivoting, data blending and table calculations to give you your desired output. The type of analysis and visualizations you can do might be limited by the way your data is currently structured.

               

              Step 1: Connect Tableau to Data Set 1 (Excel). Select Component A, Component B and Component C columns. Add them to pivot.

              Step 2: Connect Data set 2 (OData) as a separate data source

               

              Step 3: Go to Sheet 1, Right click on [Product] in the measures pane and select 'Convert to Dimension'

               

              Step 4: Create the following calculated fields in the "1st Dataset" data source

               

              [Component]

              [Pivot Field Values]

              [Component Mixture]

              [Pivot Field Names]

              [Component A Cost]

              ZN(IF ATTR([Component Mixture]) = 'Component A' THEN SUM([Component A %]) * SUM([2nd Dataset].[Price]) END)

              [Component B Cost]

              ZN(IF ATTR([Component Mixture]) = 'Component B' THEN SUM([Component B %]) * SUM([2nd Dataset].[Price]) END)

              [Component C Cost]

              ZN(IF ATTR([Component Mixture]) = 'Component C' THEN SUM([Component C %]) * SUM([2nd Dataset].[Price]) END)

              [Total Price]

              WINDOW_SUM([Component A Cost] + [Component B Cost] + [Component C Cost])

              [Row Filter]

              FIRST() == 0

               

              Step 5: Blend both data sources on [Component]

               

              Step 6: Create the view as shown. [Total Price] and [Row Filter] are table calculations. Set each of them to compute using Pane (down). Right click on [Component Mixture] on the rows shelf and uncheck "Show Header".

               

              Hope this helps.

              Ossai