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:
- The top elements (in this example, five states)
- The exclusion elements (for calculating the sum, these are not displayed)
- 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.