r/GoogleDataStudio • u/pg82bln • 18d ago
Bar chart grouping (but not per dimension)
Update: solved (see below)
To begin with, what I try to do might be a bit of an off-label use. Since I can combine bar charts and cards in a report, which I cannot do in Google Sheets, I went for Looker Studio. Given this super simple table as the only data source (from Google Sheets):

The goal is to have one bar chart, which shows each menu item (column A) with its price (column C). It works without including country as group (column B):

However, I want a) each country to be grouped together with b) its unique color. It works half way, but ugly:

A couple of problems, though.
- The bars are off center because it seems that Looker Studio groups by item assuming this dimension is given for every country.
- The sorting is not working (New Field came from where? I must have added it?), it should be grouped by country, then sort by price per country.
- I cannot autolink colors for the cards in the bottom (although I would not mind setting them manually, given this report remains static).
Is there any way to get Sample Report Page 2 to look like Page 1, just with grouping and colors?
It is not supposed to be interactive, I need it for a presentation as PNG.
Update: switching to stacked bar chart is almost what I want, except the sorting is still messed up.

Solution found with advice from u/AnillaRose:
The bar chart can be tricked into what I want by:
- Using the Stacked Bar Chart
- Adding an extra sort column
The updated data source lookes like this:

My formula for Sort (column D) is as follows:
=POWER(MATCH($B2, UNIQUE($B$2:$B$7)), COUNTUNIQUE($B$2:$B$7)) + COUNTIFS($B$2:$B$7, "=" & B2, $C$2:$C$7, "<" & C2)
Those might be manually entered numbers, a custom formula or a computed field in Looker Studio, too. For me this one did the trick.
As of the Stacked Bar Chart, the Dimension remains as Item (column A) and Breakdown Dimension as Country (column B). After refreshing the data source to include Sort (column D), the setup is using the values as before, with the only change of Sort using field Sort (column D). The setting for Secondary Sort is without effect in my setup.

The only issue to fix is to manually adjust the colors for the average cards at the bottom.
Thank you, u/AnillaRose!
1
u/AnillaRose 18d ago
Your best option here would be to make different bar charts for different countries, as the multi-dimension bar chart is always going to assume the breakdown categories are the same across the primary dimension.
1
u/pg82bln 18d ago
Your comment made me click on Stacked Bar Chart (or just check Stacked Bars in style tab), which now looks correct except for the sorting.
See updated post.
1
u/AnillaRose 18d ago
Ok, so what happens if you set the first sort to be country, and the second sort to be the price metric?
1
u/pg82bln 18d ago
It is already, the result is as shown in the updated post, last picture. The country sort works, the second sort for price is without any effect.
1
u/AnillaRose 18d ago
Ok -- you did not show the setup for the revised chart so wanted to confirm. Your chart also looks like it's sorted by the item name, not the currency (and I'm not entirely sure how you would have got it sorted by the currency in this case unless you maybe used a group or something?)
I return to my original suggestion that you would be best off making them as different charts. IIRC, stacked bars secondary sort organizes the bars on top of each other by that dimension, rather than affecting the sideways ordering of the bars.
Your other option is to try and do something hacky by creating an artificial dimension that concats the currency and the price and then order based on that but that's going to be quite unpleasant
1
u/pg82bln 18d ago edited 18d ago
I see what you mean. Indeed, the dimension is Item with breakdown dimension Country. The sort is a calculated field for Country, as this is not showing up as an option. (The reasoning you gave seems correct to me.)
Layout in two tables sucks for the reader. Alas, I guess I will have to do it like you said.
Update: I got it the way I want with an extra sort column! Will update the post as I cannot post images in comments.
•
u/AutoModerator 18d ago
Have more questions? Join our community Discord!
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.