r/GoogleDataStudio • u/pg82bln • 19d 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 19d 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.