Clustered columns with unpivot input table

[tatoon]

I have an input data with this structure:

Region	Country	City	Year	Month	Metric	Value
EMEA	France	Paris	2021	1	Sales	1000
EMEA	France	Paris	2021	2	Sales	900
EMEA	France	Paris	2020	1	Sales	750
EMEA	Germany	Berlin	2021	5	Sals	500

I would like to create a clustered column charts where X-axis is the months (1-12) and the columns represent the years (so for example pairs of two columns, one for 2020 and another for 2021).

So the chart might compare the sales by month for Paris for the years 2020 and 2021.

With my current table structure I cant get this chart.

Ideally I don't want to pivot the table (if possible) since in the dashboard I want to add other charts with different combinations in the columns.

[HotChilli · Accepted solution]

[tatoon]

Nice! That works.

One more thing, if I had a third year (ie. 2022), would it be possible to add it on top as a line (ie. line and stacked column chart)? I tried, but I think it's not straightforward.

[tatoon]

The idea is a table like below. So the year columns have to be side by side (instead of first 2020 columns and then 2021).

I can easily get this chart if I pivot the original table, but I have many other columns and will use the input data for other tables and charts in the same dashboard.

[HotChilli]

Can you mock up a quick picture of what you want so that we are clear about the requirements?