6 Replies Latest reply on Sep 13, 2016 8:03 AM by Archit Sood

    Top 80% contributors ??

    Archit Sood

      So I am trying to find top 80% of locations where things happened and keep other 20% as others. Essentially, which all locations contribute towards top 80% should be in list and all others in "Others".

       

       

      LocationNumber of events
      a45
      b40
      c22
      d14
      e2
      f1
      g1
      h1
      i1
      Total127

       

      Issues I have ran into:

      1. Is it that simple, 80% of 127 is 101 (aaprox). So top location where i reach that number, should be the locations that be part of top 80% and rest in others?

      2. The top 3 sum is 107, so how do i ensure that location 3 and its events are picked up if i create a filter in tableau and apply it.

      3. How to do all this in tableau?

       

      Below is the screen shot on which I am trying to apply this logic.

        • 1. Re: Top 80% contributors ??
          Ben Whitaker

          Hello,

           

          Not quite - using your method it would mean that none of the locations would fall above the 80%. I think what you want to do is take 80% of the number of events of the top location. So do that by doing the following calculation, maybe call it threshold:

           

          WINDOW_MAX(MAX([Number of events]))*0.8

           

          Then you could create another calculation:

           

          IF SUM([Number of events]) >= [Threshold] THEN ATTR([Location]) ELSE 'Others' END

           

          Drag the new calc to your view alongside the original location, hide the header of the original location, and make sure the new calc is computing along location.

           

           

          Packaged workbook attached. In the packaged workbook, I have actually just parameterised the threshold so you can change that if you need to....

           

          Thanks

          Ben

          1 of 1 people found this helpful
          • 2. Re: Top 80% contributors ??
            Kristy McGee

            Hello Archit,

             

            I have been using a grouping similar to the above, but with a dynamic grouping.  If you would like to see a sample workbook, please view this link.

             

            https://public.tableau.com/views/NoData/TopNDashboard?:embed=y&:display_count=yes

             

             

            It is a little more complex than just the top n, but I think it will solve your issue.  If you have any questions, please let me know.

             

            Thanks,

            Kristy

            1 of 1 people found this helpful
            • 3. Re: Top 80% contributors ??
              Archit Sood

              Thank you Ben.

              But if I do the threshold (80% of the max value), and from the list if I only have 1 above 36, then it would mean only location "a" contributes towards 80% of the event which would not be true.

              Correct me if I am wrong!

              • 4. Re: Top 80% contributors ??
                Archit Sood

                Thank you Kristy.

                 

                Though its an awesome dashboard, but trying to put % instead of just a number is causing trouble.

                • 5. Re: Top 80% contributors ??
                  Ben Whitaker

                  Hi Archit,

                   

                  Yeah I must admit I misread your original post... I have gone back and amended the way this is calculated, I hope this is what you are looking for. The technique is similar to my original post, but now I am ranking your locations based on the number of events. Create the following calculated fields:

                   

                  Index

                  INDEX()

                   

                  Threshold

                  TOTAL(COUNTD([Location]))*0.8 (or change 0.8 for a parameter that controls this number)

                   

                  Location Formatted

                  IF [index] >= [Threshold] THEN ATTR([Location]) ELSE 'Others' END

                   

                  Drag all of these on to your sheet and make sure the following table calc is applied to each of the above calculated fields:

                   

                  Computing along Location with a custom sort of sum(number of events) ascending.

                   

                  This will give you:

                   

                   

                  As the table calcs are across specific dimensions you can drag all the fields you dont want onto details and it won't mess up the figures.

                   

                  This has ranked all locations based on number of events and got the top 80% based on the ranking. There may be an easier way to do it, but this is what I have came up with. See attached workbook for reference, you can amend the parameter control to change it to 70% or 60% etc.

                   

                  Thanks

                  Ben

                  • 6. Re: Top 80% contributors ??
                    Archit Sood

                    Works perfect.

                     

                    On the same note, how do I club the others as one section? With Top N, its easy as you create a parameter, set and write a simple if else. Any suggestion?