Google sheets bar chart with multiple variables

Question

I am trying to create a google sheets bar chart showing instances/count of categories/variables (A,B or C) per year.

I’ve managed to create the following:

A - chart showing count/instances of the variables. However, this is not divided by year.

B - showing total count of the variables per year. However, this is only showing total count, not the division/proportion of categories in this count.

But what I want to do is something more similar to this (preferrably the first one, showing division and total Count in the same bar)

chart example

Link to example sheet:

https://docs.google.com/spreadsheets/d/1aM1o3Qpqq18pfTtIl3DOu9vFD8u7ouNrX1G87UtLGs4/edit

Is this possible?

Answer 1

you will need to transform your dataset to:

=QUERY(A2:B, "select count(B) where B is not null group by B pivot A")