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....
Example.twbx 25.9 KB
1 of 1 people found this helpful
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.
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!
Thank you Kristy.
Though its an awesome dashboard, but trying to put % instead of just a number is causing trouble.
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:
TOTAL(COUNTD([Location]))*0.8 (or change 0.8 for a parameter that controls this number)
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.
Example.twbx 20.4 KB
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?