Grouping Lower Ranked Slices in Pie Chart

I have written a blog on the same topic a while ago, based on creating sets. The sets approach is good when no filter is in action. The approach described here can be more general.

This is a companion post to Grouping Lower Ranked Bars in Bar Chart. We will use most of the same calculated fields.

Step 1. Create an integer parameter [Top N].

Step 2. Drag [Country / Region] to Details shelf and sort it by Sum(Sales) in descending order.

Step 3. Create a [Top N+1 Rows] filter:

• Index()<=[Top N] +1

Drag it to Filters and set it to Compute using> [Country / Region]. Select True at pop up.

Step 4. Select mark type to be Pie chart.

Step 5. Create [Top Countries & Others]. This will alias the country at the (N+1)th position to be "Others". Drag it to Color shelf and Label/Text shelf and set it to Compute using> [Country / Region].

• If Index()=[Top N] +1
• Then "Others"
• Else Attr([Country / Region])
• End

Step 6. Create [Top Sales & Others]. At the (N+1)th position, the value is altered to be an aggregated result. Drag it to Angle and set it to Compute using> [Country / Region].

• If Index()=[Top N] +1
• Then Window_Sum(Sum(Sales),0,Last())
• Else Sum(Sales)
• End

Note that in the above formula, the If statement sets the index=0 for the window function which has its own index. The settings here instructs the window function to compute from (N+1)th row to the last row.

That's it! The workbook can be found here.

Top N & Grouping Others Series

Grouping Lower Ranked Bars in Bar Chart

Grouping Lower Ranked Slices in Pie Chart

Grouping Lower % Bars in Bar Chart

Grouping Lower % Slices in Pie Chart

Dynamic Pie Chart: Top N, Sorting Slices and Grouping Others