2 Replies Latest reply on Nov 6, 2018 4:34 AM by Frazer Nash

    Commonality of Sub-Category across Categories

    Frazer Nash

      I have a table of manufactured items in one row and one of its components in the row next to it. I can't attach my data but as an example:



      I want to create a table so that you can compare products one and two and see how many components are common. See example below:



      So you could use the table to quickly check that products 1 and 3 share 2 components.

      I am using a portion of a dataset at the moment that has 45 products each with around 50 components.


      My issue is that if you put the rows and columns both as Product then it will only show how many components for 1 & 1, 2 & 2, and 3 & 3, due to the nature of how Tableau carries that data.

      I decided to test creating a data set with two product columns and one component column, and there was a row for every link. But for 45 products that generated 1.3 million rows, which will not work when I include the rest of the products I'm researching.

      I was hoping there would be a way to format the table in Tableau or use calculated fields/parameters.


      Is this achievable in Tableau?

        • 1. Re: Commonality of Sub-Category across Categories
          Zhouyi Zhang

          Hi, Frazer


          It is possible, but not sure how big your real data is, but just give it a try


          1st, you need join your data to itself as shown below using a key 1=1


          2nd, after join, you will have two produce fields, and drag one to column and the other to rows.

          3rd, create a calcuation field to count the common component


          A sample workbook attached for your reference as well.


          Hope this helps



          • 2. Re: Commonality of Sub-Category across Categories
            Frazer Nash

            Hi Zhouyi,


            Thank you for your response.


            I was hoping to find a different solution as this creates too many data entries. from the 10 rows in the original data set it has increased that to 100 rows.

            With my data set I have many products, I am creating a dashboard with just a sample of 45 products at the moment. But with just 45 products this method generates over 1 million rows, this will not be a viable solution for my full data set. I was hoping for a solution that did not generate any more rows.