7 Replies Latest reply on Feb 24, 2016 8:55 AM by Ben Page

    Product ranking with mapped products of competitor

    Jaroslaw Suchanek

      Hi,

       

      I have created Bar Chart with Revenue of Products of Vendor A.

       

      I have excel file with mapping, which compares Products between Vendor A and Vendor B. In other words based on this excel file I know which Products are similar.

       

      I would like to blend excel file with my data source and create another Bar Chart with Revenue of Products of Vendor B corresponding to Products of Vendor A.

       

      In other words if on Chart 1 I would have Revenue of Product A001 as first one, then on Chart 2 I would like to see Revenue of corresponding Product of Vendor B. For example Product B001.

       

      Do you have any idea how to create such visualization?

       

      Kind regards,

      Jarek

        • 1. Re: Product ranking with mapped products of competitor
          Shinichiro Murakami

          Jaroslaw,

           

          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"

          "Prod. "+right([Product],1)

           

          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.

           

          Thanks,

          Shin

           

          9.0 attached.

          • 2. Re: Product ranking with mapped products of competitor
            Jaroslaw Suchanek

            Hi Shin,

             

            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.

             

            Kind regards,

            Jarek

            • 3. Re: Product ranking with mapped products of competitor
              Ben Page

              Jaroslaw,

               

              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:

              current shape.PNG

              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:

              ideal shape.PNG

              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.

               

              Thanks,

              Ben

              • 4. Re: Product ranking with mapped products of competitor
                Jaroslaw Suchanek

                Hi Ben,

                 

                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?

                 

                Kind regards,

                Jarek

                • 5. Re: Product ranking with mapped products of competitor
                  Ben Page

                  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:

                  new data.PNG

                  You'll notice that the bottom chart is sorted based on the top chart's revenue. Let me know if this helps.

                   

                  Ben

                  • 6. Re: Product ranking with mapped products of competitor
                    Jaroslaw Suchanek

                    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.

                     

                    Kind regards,

                    Jarek