How to chart the Top 5 along with an aggregate of the Other

In a Question in the Q&A Section of the Community User Bruce Campbell asked how to combine the Top 5 of a list with an Aggregate of the Rest. We got a very good answer from samuel alma . To avoid that this gets lost, please find the Solutions to this Question prepared as a Tip& Trick.

This is the Chart visualization that was asked for:

A: Build a top 5 List and a formula to aggregate all others

To do it from the ground up by hand, you would first by build a top 5 list and additionally a formula to group and sum up all the others. After saving them you can select these items and show them in the chart.

B: Use right click Options to achieve the result

But this can also be achieved easier by using the quick calc items you can reach with a right click.

A) Static List

1. Start in a grid (its easier to see and select). In the grid (I'm using the sample DB), sort Dimension high to low and then manually click on the first 5 items. Then right click, and choose the List and Group option.
2. This will give you a static view on the top 5 items in the Dimension. If static is enough. You can now switch to the Visualization of your choice and show it in the chart.

B) Dynamic List

But, if you want to make the top 5 dynamic, then there are a few more steps to do.

1) 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.

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

3. 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]})}
)

Copy

You can simply delete the items and then drag the list object into the same spot in the formula.

Save the Formula and use them in your visualization. Now you will get the result as asked above.

 Use the Result in all types of Visualizations.

The mechanism to pick elements or design calculations is entirely INDEPENDENT of how they are visualized. The trick is simply to put the right things in the right drop zones. Here is the same selection shown in various visualizations - including a pie chart.,

Grid

Bar Chart

Pie Chart

Tree Map Chart

And that's it. Thank you samuel alma for this solution.