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?
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
[Pivot Field Values]
[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)
WINDOW_SUM([Component A Cost] + [Component B Cost] + [Component C Cost])
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.
Product Components Price_OO.twbx 18.1 KB