Chart Top 5 Along with an Aggregate of the Others
We have charts that display the top 5 countries; How can we aggregate the other countries that are not in the top 5 and display them in an "Others" bucket, as below?
5 replies
-
This can be done from the ground up by hand, first by building a top 5 list and then a formula to represent all others. You then select these items and show them in your chart. But I would do this with some of the quick calc items to accelerate this exercise.
First, I would do it in a grid (its easier to see and select). In the grid (I'm using the sample DB), I sorted the products high to low and then manually clicked on the first 5 items. Then right click, and choose the List and Group option. If you don't have a dynamic list of top 5 products or countries, this is the end. You just need to show it in a chart.
But, if you want to make the top 5 dynamic, then there are a few more steps to do.
If you look in the elements tree for products, you see 2 items created: The 5 items picked and the "All others'. We need to edit these to make it fully dynamic. Start by making them sharable items.
Then right click on the list first, open it in Formulate, and edit the formula. Switch out the selection to the entire list of products (countries) and then add a top count filter.
Next, open the 'all others' in formulate. This is where it may get messy. You need to replace the specific product listing with the set (row 2 below).
Aggregate({Except({AllMembers([products].[Product])}, {[products].[Product].[Touring-2000 Blue, 60],[products].[Product].[Touring-3000 Yellow, 58],[products].[P roduct].[Touring-3000 Yellow, 44],[products].[Product].[Touring-3000 Blue, 62],[products].[Product].[Touring-1000 Yellow, 60]})} )
You can simply delete the items and then drag the list object into the same spot in the formula.
And that's it.