3 Replies Latest reply on Apr 30, 2018 4:20 AM by kumar bharat

    Index that sums amounts of top 5 clients on total amounts

    Angelo Saponara

      Hello Everyone,

       

      I have to produce a new measure that sums up the amounts of the 5 top customers and divides it for the total amounts. It should be possible to change the perimeter of the top 5 according to the filters used. For instance if we work with a dataset similar to this example below:

       

      The index should work with filters in a way that If the level selected is "Shop" level the calculation should result in:  Customer1 (130 i.e 100+30) +Customer2 (70) +Customer5 (60) + Customer4 (55) + Customer3 (40) = 355 divided by the sum of all the customer sales for shop1 ie 375. The index therefore should be = 96.6%. While for Shop2 the index should be 340 (C7+13+9+14+10)/410 = 82.9%

       

      However, if level selected is "region" then the index should show the top5 per region (regardless of the shop) ie for Region1 (C1+C2+C4+C3+C10) / the total for region 1  = 335/370 = 90.5%

       

      I tried many possibilities whit the running window but nothing suited my analysis. It should be a measure and not just a visualization.

       

      Anyone with a solution?

       

      Best,

      Angelo

        • 1. Re: Index that sums amounts of top 5 clients on total amounts
          kumar bharat

          Hi Angelo,

          Please check the below,it should help your case.

          1. Tableau Public
          2. Tableau Public
          3. Tableau Public

          hope it helps u.

          BR,

          bharat

          1 of 1 people found this helpful
          • 2. Re: Index that sums amounts of top 5 clients on total amounts
            Angelo Saponara

            Dear Bharat,

             

            Thank you very much for the inputs provided. I took advance of them and tried to apply them to my case, however while being very close to the solution I there still a missing piece. This is my result table (created with My Superstore just to show the example):

             

             

            Concretely used Rank_unique with a parameter in order to create 3 calculated fields that calculate in turn:

             

            Sum of top N shipping cost = Sums the shipping costs for the top N (depending on the filters). in this case the top N customers in each category of product for that ship date.

            Top N shipping cost on total costs = Sums the shipping costs for the top N (depending on the filters) [i.e. the previous calculated field] and divides them for the total shipping costs of that category

            Window sum of Top N ratio = Sums up the ratios created with the previous ratio (however in the view it is repeated 5 time because Table shows it for each of the top 5 customers).

             

            However, while I would like to have the calculations exactly like this (i.e. top 5 by customer in each category) I would like Tableau to show a single datapoint and not the bars split by customers as well as in the third chart for the window sum there should be only one bar and not to repeat the same number 5 times (in which case the 2 and 3 chart should be the same and for me it is important that either the second or the third work, I did the third hoping that the window sum would have shown only an aggregate of the 5 stacks of the second chart).

             

            The result should look like:

             

            I attached the workbook where I created this. All the measures that I added are under "Created measures for example" folder under measures.

            • 3. Re: Index that sums amounts of top 5 clients on total amounts
              kumar bharat

              Hi Angelo,

              Workbook is missing in your reply however i don't have tableau installed in my system to help you further.

              Sorry about that but the links i shared earlier are perfect.Please recheck.

              BR,

              bharat