Yes, but it is difficult to guess your request without workbook, anyways, I created something.
First of all you need to create universal Prod.ID to compare company A and B.
[Universal Prod.ID] // required to be put for both datasource to be used as "Relation link"
Set relation between two files as below.
Company A as original data. Company B as excel additional data.
Rename "Sales" to Sales A for company A and Sales B for company B
Then create charts as you like.
ComanyAB 9.0.twbx 32.2 KB
My workbook is different than in your example, sales figures and every other data is only stored in the Main Data Source. Excel used in blending only contains 2 columns with Products of Vendor A and corresponding Product of Vendor B. It should be noted that names are complicated, they are not like 'Product 1', they are more like 'XYC-20312-BCD'.
I can't share real data, but I have created sample workbook with minimal amount of data to show what I have at hand.
End result should look something like that:
Bar Chart 1 with Top 5 Vendor A Products ranked by Revenue.
Bar Chart 2 with corresponding Vendor C Products. Ranked accordingly to Chart 1.
Example.twbx 19.7 KB
This is a tricky problem. On the one hand, you need to establish a relationship between the two data sources on product. At the same time, you would like there to be a relationship between Product and "Vendor C Best Fit" so that when you choose Vendor A, you can see revenues broken out by the C Products. The way I see it, you can proceed a few ways:
1. Don't change the data
If you leave your data as is, you can duplicate your main data source and use your blended source as the primary for both sheets. I have done this in "Option 1." I might be making an incorrect assumption, but if Product A1 is the closest match to Product C1, then Product C1 should also be the closest match for Product A1. So, when you select Vendor C in the dropdown, you should see the reversed charts as you would when Vendor A is selected (not always necessarily, but just based on the sample data you've provided). Currently, this two-way relationship does not exist:
So, with your sample data, this will show you the "correct" (based on my assumption) output only when "Vendor A" is selected.
2. Duplicate your rows in blended data source
By restructuring your data to create this two-way relationship between products and corresponding products, your data will look like this:
You use the exact same approach as Option 1, but the dashboard will show the correct corresponding products for Vendor C as well. "Option 2" in the workbook.
3. Duplicate your rows in blended data source, and add revenues for corresponding products
This will allow you to use your main data source as the primary for both sheets. Eliminates the number of data blends, doesn't contain null in the Vendor drop-downs, and will show the top Chart for Vendor B.
Let me know if this helps or if I have misunderstood your requirements.
Thank you a lot. It seems that I might be able to re-create your analysis in my data source.
The only issue I am thinking of right now is related to ranking. In sample data source Products had Revenues were already sorted/ranked. In other Words Product A1 was corresponding to Product C1 and so on.
But what if corresponding Products are not in alphabetical order, e.g. Product A1 is related to product C3. And what if their Revenues are not sorted.
Right now Product with 1 have highest Revenue and their are related to each other, therefore everything works fine. But if it was not true, then how do I make sure that both Charts are ranked according to Vendor A Products' Revenue?
Check out my updated workbook. I've gotten rid of option one and two. In order to ensure that you're bottom chart is sorting correctly, you just need to create a calculated field that sorts based on the original product's revenue, rather than the corresponding product's revenue. I've switch around some of the relationships in the data:
You'll notice that the bottom chart is sorted based on the top chart's revenue. Let me know if this helps.
Thank you for once again Ben .
Your idea seems to be really good, however I have to confirm with back-end developer if he can enrich my excel file with Revenue data and refresh it on automatic basis.
Glad to help out