The world’s leading publication for data science, AI, and ML professionals.

How do you custom sort categorical data in Google Data Studio charts?

A couple of workarounds for a missing feature in Google Data Studio

Photo by Maksym Kaharlytskyi on Unsplash
Photo by Maksym Kaharlytskyi on Unsplash

Data studio is a free tool from Google that allows you to build dashboards and reports. You can connect to data through a variety of sources including BigQuery, MySQL, PostgreSQL, sheets or even uploading CSV files. Once you’ve got your data loaded, you can build basic charts, drop downs, ‘big number’ boxes and text. The look and feel is ‘functional’, rather than beautiful.

There are some functional limitations, and it is not in the same league as Looker (also owned by Google now), or Tableau. To be fair, it is a free product (both to build and to view) and I do have to remind myself of that, but it’s definitely not my favourite tool.

I ran into a problem this week, which should have been easy to do, but I had to find a workaround (which wasn’t that easy to find).

The problem

There is no obvious way to change the order of categorical data in pie charts or bar charts in Google Data Studio. By default, the number of responses is used, which doesn’t make sense where these is a an order to the data.

If you want to change the order in which your data shows in Data Studio charts, then this is the article for you!

The objective

I wanted to make a bar chart showing the number of customers broken down by the amount that their relationship balance has changed. The data is already prepared into the following categories:

  • Decrease >10%
  • Decrease <10%
  • Increase <10%
  • Increase >10%

There is clearly an order to this data (i.e. it is ordinal). But for some reason Google Data Studio makes it difficult to sort this data within a chart. By default, the chart is sorted by the records. However it would be far easier to read the chart if ‘increase >10%’ was at the bottom of the chart.

Image by Author
Image by Author

There are 2 options to solve this, depending on what you want the labels of the graph to look like.

Option 1

Option 1 will not change the way your labels look. You will need to create a new field and use a CASE expression to create a number depending on the name of the categorical variable.

Within the ‘Data’ pane add a new field:

Image by Author
Image by Author

CASE works in the same way as ‘IF’ in Excel. It evaluates the first part to see if it’s true, and if it is, then it will return a number. You write a ‘WHEN’ for each label within your data. Make sure the numbers put after the ‘THEN’ are the order you want!

You drop your newly created field on the sort, and it should work. Sometimes it can get confused depending on the aggregation method, by default it normally picks ‘SUM’ but if it’s not working try another aggregation method. Mine only worked with average.

Here is the final result. Now it’s clearer to see that the control group are much less likely to have an increase in their balances, and much more likely to have a big decrease.

Image by Author
Image by Author

Option 2

The second option is to change the way you label your data with a number in front, and then sort alphabetically. The data labels will show this labelling, as Google Data Studio doesn’t seem to have a feature to relabel variables in charts.

Image by Author
Image by Author

To change the way the data sorts, you drop the dimension you want to sort onto the ‘secondary sort’.

Image by Author
Image by Author

This is what is looks like after sorting. The downside of this option is that you can see the numbering in the legend.

Image by Author
Image by Author

For a free tool, Google Data Studio does the job, but these fiddly things do make it frustrating.

Note: this data is synthetic to demonstrate the problem.


I like to write about Data Science for business users and I’m passionate about using data to deliver tangible business benefits.

You can connect with me on LinkedIn and follow me on Medium to stay up to date with my latest articles.


Related Articles