How to sort the bar chart considering the category in Google Data Studio?

Question

I have been trying to change the order of the bar chart, so that the values for "Positiva" would appear first, then followed by "Negativa" and "Não sei". After reading some information on Google Data Studio forum, I saw that I should create a formula and then add it to the sort option. But it still didn't work.

The image below shows my bar chart. The category in green should come at first, followed by red and gray. I don't want to change the colors, I want to change the order.

So, after reading the forum, I created this formula and added to sort option, but I got an error. How can I solve it? And is there a way to centralize the percentage?

Answer 1

It can be achieved by using the Calculated Field below as the Secondary Sort, and setting the Order to Descending:

AVG(CASE
    WHEN REGEXP_MATCH(answer, "(?i)(Positiva)") THEN 3
    WHEN REGEXP_MATCH(answer, "(?i)(Negativa)") THEN 2
    WHEN REGEXP_MATCH(answer, "(?i)(Não sei)") THEN 1
    ELSE 0 END)

Google Data Studio Report and a GIF to elaborate: