Pie chart: group remaining values as “Others”

Looker Q&A Looker
,
1

Hi everyone,

I created a pie chart where the dimension has many values. I would like to show only the top 6 values and group the rest under “Others”.

I only found the Row Limit option, but that just removes the remaining values instead of grouping them.

Is it possible to group the remaining values into an “Others” category? If so, what is the best way to do it in Looker?

Thanks in advance!

2

This is the solution we keep in our internal wiki, it is based on an old Looker blog post that is no longer available.

Hope it helps!

For this recipe, you’ll need the following ingredients:

  • 1 dimension that you will use to label your chart segments

  • 1 measure to determine the magnitude of the chart sections

  • 2 table calculations

    • One to replace the dimension and the other to replace the measure value

In your explore, you’ll also need to turn on “Totals” using the checkbox in the data pane and set the row limit to equal how many dimension options to include plus 1 (n+1). For example, for top 10, you’d set the row limit to 11. (10 + 1)

First, we’ll create the table calculation to replace your dimension.

Working Code:

if(

# For rows other than the last row

row() != max(row()),

# Display your dimension

${dimension_for_top_10},

# Otherwise, label as other

“Other”

)

NOTE: You may need to account for when you data is returning fewer rows than your n+1 value.

What’s happening here?

First, we check if the current row number is equal to the max row number in our result set.

If it isn’t, display your original dimension.

If it is, e.g. the last row in your result set, set the label to “Other”

Next, we’ll create the table calculation to replace and summarize your measure.

Working Code:

if(

# For rows other than the last row

row() != max(row()),

# Display the sum your metric

${metric_value},

# For the last row, group them as other

${metric_value:total} -

sum(

if( 

    row()= max(row()) ,  

    0 , 

    ${metric_value} 

  ) 

)

)

What’s happening here?

First, we do the same check as before. See if the current row number is the last one in our results.

If it isn’t, use your original measure value.

If it is, we have some additional steps.

Start by taking the sum total of the entire measure column (displayed AND hidden) using the :total command.

Then subtract off a conditional sum based on the same row number check as before. If the row number is the last row, set it to 0; We want to include that in our Other bucket.

If it isn’t the last row, include it in our conditional sum. The result of this will be the sum of our Top N dimension (e.g. - Top 10)

Subtracting this out of our total will leave us with the sum of the entire measure column for all our hidden rows, which is now the measure for our “Other” dimension.

Finally, you’re ready to pop this cake in the oven

Add your original dimension, original measure, and two table calculations to your data grid.

Hide the original dimension and original measure from the visual.

Sort by your original measure in descending order.

Double check totals are on and you’ve set your row limit and voila, time to dive into your tasty “Other” Viz

1 Like