1

Top N Items Plus Remaining Items Using Pie Charts

by Dirk Jung, Sales Engineer


Sometimes charts become difficult to read if too many values are contained in a pie chart. When there are too many slices of the pie, the chart is not truly helpful.

To remedy that situation, you can create pie charts that display unique slices for your top items and one slice for all remaining items. The example below shows the top 5 states by turnover and the rest of the states. The top 5 states each display as their own slices, while the rest of the states display as one slice.

This requires three user-defined elements:

  1. The top elements (in this example, five states)
  2. The exclusion elements (for calculating the sum, these are not displayed)
  3. The sum of the exclusion elements

Final display of top 5 items plus aggregation of remaining members.

 

Create User-Defined Elements

Step 1: Create user defined element top 5 states

Use the Advanced Set Designer to create the top 5 states. Select the Customer Geography Zip dimension and the State hierarchy.

Select Customer Geography Zip and State

 

Then, filter the elements by Top Count, select 5 and Sales. Name this "Top 5 states" and select OK.

Creating a top 5 filter using Advanced Set Designer.

 

Step 2: Create the exclusion element

Now, we are going to create the exclusion element using the Set Designer. Again, select the Customer Geography Zip dimension and the State hierarchy.

Select Customer Geography and State.

 

This time, we are going to exclude the pre-built “Top 5 states” element by using the Except/Remove function. Name this "Top 5 Exclusion Element" and select OK.

Add the other members.

 

Step 3: Create the sum of the exclusion elements

Select the “Top 5 Exclusion Element“, open the context menu and select Member Calculations, Aggregate Functions, then Aggregate.

Choose to Aggregate the Elements.

 

The calculated rest is automatically displayed in the Custom Member designer.

Aggregate the members minus the Top 5 states.

 

Step 4: Display the Top 5 States and Rest in BI Office

Select the Top 5 states and the Rest Top 5 States elements and click Run Query.

Select the User-Defined Elements.

 

Finally, we create the report.

Make the selection to show the final result.

Reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
Like1 Follow
  • 1 Likes
  • 2 yrs agoLast active
  • 1132Views
  • 1 Following