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

# Top 80% contributors ??

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".

 Location Number of events a 45 b 40 c 22 d 14 e 2 f 1 g 1 h 1 i 1 Total 127

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 ??

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 ??

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.

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 ??

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 ??

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 ??

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 ??

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?