0

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

null
    • samuel_alma
    • 1 yr ago
    • Answer
    • Reported - view

    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.

      • Bruce_Campbell
      • 1 yr ago
      • Reported - view

      Fantastic!  Thanks very much    

      • Ying_Dong
      • 1 yr ago
      • Reported - view

      Tried this and works great for grid and bar chart, I started as grid and split Horizontal, then I changed one of them to bar chart and works automatically, but not for pie chart. There is any solution for pie chart by any chance?

      • samuel_alma
      • 1 yr ago
      • Reported - view

      This works with ANY visual. 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 the pie chart.

       

       

       

      • Ying_Dong
      • 1 yr ago
      • Reported - view

      Yes it works now

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 5Replies
  • 59Views
  • 5 Following