6 Replies Latest reply on Nov 29, 2016 9:39 AM by Kevin Wang

    A Level of Detail Fixed Set?

    Kevin Wang

      Hi Tableau Community. I have an interesting question, and I'm not quite sure if it's possible in Tableau.

       

      Basically, I am trying to create a view that shows a color mark for a top 10 set. However, I want the top 10 set to be per partition, rather than for the entire view.

       

      So let's say I have a product category. I would like to visualize what percentage of total sales in each product category the top 10 products in each product category represent, across all product categories. This way, I can quickly see which of my product categories are least diversified. The tricky part is somehow having the 'top 10 set' change in each partition.

       

      I managed to do something close with table calcs, but the issue with table calculations is that it forces the products to be disaggregated, otherwise I cannot "rank" the products.

       

      Capture.PNG

       

      Please let me know if I need to throw together a mock-up with superstore data. Thank you Tableau Community!

        • 1. Re: A Level of Detail Fixed Set?
          Stephen Rizzo

          At first glance I don't see a solution using LOD expressions. With that said, I am not sure I understand the problem with table calculations. How does the attached workbook compare to what you are looking for? If you expand out the [Product] hierarchy in the rows shelf, you will see that the top 2 sub-categories are being dynamically selected based on SUM([Sales]). It looks quite similar to the image you displayed, but with a sort on [Sub-Category].

          • 2. Re: A Level of Detail Fixed Set?
            Kevin Wang

            Thank you for the quick reply. The problem with table calcs is the fact that the data must be disaggregated. Because the natural followup question to displaying the data this way is to ask, "what percentage of my total sales does the top 10 products take up?" However, with the marks disaggregated by product, you cannot calculate the "top N" ranked items as a percentage of the whole. You can make a visual approximation, but no actual numbers.

             

            Am I missing something? Thank you for all your help so far.

            • 3. Re: A Level of Detail Fixed Set?
              Stephen Rizzo

              You can get the number using (computed over [Sub-Category])

               

              WINDOW_SUM([Is rank 1 or 2?]*SUM([Sales]))/WINDOW_SUM(SUM([Sales]))

               

              You may need to play around a bit to display the value where you want it.

               

              Alternatively you can do the ranking in a pre-processing step (using SQL for example), which would make this whole process much cleaner.

              • 4. Re: A Level of Detail Fixed Set?
                Kevin Wang

                Interesting... By using 1 or 0 in the dimension, then multiplying that by the sum you are creating a pseudo filter on the window_sum. Clever!

                 

                I do have one last issue however; since the data is disaggregated, when I sort on product category, the sort actually occurs at the view level. In other words, the sub category with the second highest sales will appear second in every partition (where it exists), where as we want the sort to "restart" at every partition.

                 

                Since sorts cannot use table calcs, this might prove to be an impasse...

                 

                I think I will end up using SQL to fill in the ranks, but Tableau is such a powerful data discovery tool, I thought it would be much easier to find out which dimensions to rank by from within Tableau first.

                 

                Thanks for your help so far!

                • 5. Re: A Level of Detail Fixed Set?
                  Stephen Rizzo

                  Glad to help! If calculating the ranks in SQL is an option for you, I think that would be cleaner than trying to work this out with table calculations.

                   

                  With that said, I don't think that sorting should be an issue. Did you try editing the table calculation to restart every partition? (under "Edit Table Calculation...") Another approach might involve creating a new variable for each product category - sub-category pair so that the values are unique. If instead the issue is sorting the product categories by the percentage, there is a workaround for that - see attached.

                  • 6. Re: A Level of Detail Fixed Set?
                    Kevin Wang

                    Thanks Stephen, you've been a tremendous help. I ended up creating a combined field so that each product + sub category pair is unique, that way sorting occurs at the partition level. That being said, I do think anyone looking for a sustainable solution ought to create the rankings in SQL. The table calcs really help with data discovery, since you can do everything on the fly, make your decisions, and see the changes right away.