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

# How to look up dimension and return measure in tableau

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:

 Product Component A Component A % Component B Component B % Component C Component C % 1 Vanilla 40% Milk 60% None 0% 2 Milk 50% Sugar 40% Nuts 10% 3 Sugar 100% None 0% None 0% 4 Nuts 100% None 0% None 0% 5 Chocolate 65% Vanilla 35% None 0%

2nd data set

 Component Price 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)

 Product OUTPUT 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

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

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

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