Grouping Lower % Slices in Pie Chart

[This post is long over due. The Super Pi Day reminds me of it and thus this Pie Chart post is dedicated to the Day!]

[A sequel has been written on Grouping Lower % Bars in Bar Chart It explains the calculation from a slightly different perspective.]

A post of couple of months old in Tableau Forum has re-surfaced thanks to Patrick Van Der Hyde's effort, asking how to group lower % slices into a single one in a pie chart.

Having worked on a similar project grouping lower ranked slices before, I thought this is easy. This time, it's about grouping lower % slices. And it takes a few more steps.

The purpose in this example is to use pie chart to view the top % countries in terms of sales and group the lower % slices into one. Let's get started.

Build the Pie

1.Select the Pie chart type and make the working canvas Entire View.

2.Create a parameter [N% Chooser] which allows us to choose any % as threshold. One can skip this if the percentage is a given number.

3.Drag the [Country/Region] to the Detail shelf. Right click it and sort it by Sum([Sales]) descendingly.

4.Create a new field [Top % + Others Label] which labels all low % slices as "Others". Drag it to the Color shelf. Note that the percentage is dynamically controlled by the parameter 0.01*[N% Chooser]. One replace it by a given number if necessary.

5.Create a new field [Top % + Others Sales] which sums up the sales of all Others into one value. We will explain the logic later. For now, drag it to the Angle shelf. Right click the pill and select Compute Using>Country/Region.

Now we have a barebone pie chart that groups the lower % !

Dress up

We need to add labels to the pie.

- Drag [Top % + Others Label] to the Label shelf.
We see that there are more than one slices labeled as Others. We only need one label and need to filter the rest.

- Drag [Top % + Others Sales] to the Filter shelf. When prompted with filter settings, click OK.

- Right click the filter pill and select Compute Using>Country/Region. When filter settings pops up again, click Special tab and select Non-null values.

- Create [Percent] field and drag it to the Label shelf. Right click on it and select Format to make the numbers in %.

• [Top % + Others Sales]/Total(Sum([Sales]))

Now we are essentially done. Further cleanup is recommended:
- Click the Size shelf and adjust the size of the pie.

- Create a [Blank] field with "" and drag it to both Row and Column shelves. Then right click it and uncheck Show Header. This will remove the headers and leave the canvas clean.

- Right click the canvas and select Format. Go the grid menu and turn Row Divider to None. This will remove some unnecessary horizontal lines.

Now we have a perfect pie chart that groups the lower % slices into one, dynamically with the parameter! The workbook is here.

Calculating [Top % + Others Sales] and Filtering

The calculation is as follows:

if [Top % + Others Labels]="Others"
then if Last()=0
     then Window_Sum(if [Top % + Others Labels]="Others" then Sum([Sales]) end)
     end
else SUM( [Sales] )
end

It shows that, for the new label "Others", we need a different calculation. Otherwise it's just Sum([Sales]).

Widow_Sum() will only take into account the sales of those labeled with "Others".

There are likely multiple slices labeled as "Others". We only need one. Last()=0 will do that. The sales figures for the rest will be Null. This will be used in the filter to exclude them.

PS. Pie chart has met with great resistance in the visualization community. One of the problems is that it looks messy and cluttered when there are too many slices. Still, people are attracted to circle shapes more than others. Hope this tutorial will help creating a cleaner and more appealing pie chart and relieve the pain at the viewer side.

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