@excel_learner Couldn't really work with your file on my system. It was very slow with all the formulae and just hung up itself when I tried to add something to it. Therefore, I copied only the Data sheet to a new workbook, as values.
As you already suspected, merged cells are a challenge. Or better to say, they are a pain and should be avoided at all cost during data collection. But fortunately, PQ can fix it. If you want to make a pivot table/chart from your data, you need to unpivot the table first. But you need to merge the two header rows into one.
Merging the two first rows in the data range is done by transposing the table so that the first to rows become the first two columns which you can then merge and transpose back. But since your table is quite large, I split-up the process in two steps. See if you can follow what I did.
The attached file has the PQ output loaded to a Pivot Chart that looks like what you asked for, although I have now idea if the chart makes any sense. That's for you to judge, but I hope you can integrate this technique into your real life workbook.
