Add dots on secondary axis in grouped column chart

[sajibo]

Good morning!

I'm trying to visualise number of tests performed by 2 types of providers in 5 regions over 8 years, and the positivity ratios. So a lot of info in one graph.
The grouped columns for the number of tests look nice enough, but I can't get the positivity ratios right on the secondary axis. See Images.
When I use scatter or lines in the secondary axis, they dont show the progress from 2011-2018 like the bars do. I'd like to have just a dot at each bar for the positivity in that year, region and provider.

What's the trick?? I've managed the attached 2 graphs, but not what I want yet.

Thanks a lot!

 

[mole999]

if you install xl2bb and paste your tabke with that others can work with your data rather than replicating cell by cell

 

[sajibo]

Thanks for the suggestion mole999!
Here's my data, if anyone's willing to give it a try
I've now also tried Jon Peltiers' method described here, but it doesn't work for me since the positivity ratio scale is so much smaller than the number of tests' scale. In Peltiers method, you place everything on the primary axis, which obviously doesn't work in this case.

Ttest	Region 1		Region 2		Region 3		Region 4		Region 5
	SHC	GP	SHC	GP	SHC	GP	SHC	GP	SHC	GP
2011	314.741521	207.468185	128.887572	121.608338	39.9943518	23.4360017	44.9690618	27.4354328	147.451848	115.551208
2012	332.335631	190.05411	132.699691	119.760014	32.2955781	22.9781987	50.7425284	24.3384862	107.787451	122.775439
2013	360.657149	162.96602	137.166077	117.548703	34.8683907	22.3904281	64.7946065	22.7189247	124.157577	118.668797
2014	435.273307	137.625543	125.413475	108.322207	36.9123541	19.3204391	60.9881437	17.3171422	112.229878	99.7906056
2015	351.426287	144.940478	106.014596	109.047423		20.2926531	38.5705503	14.6548757	68.1915437	95.5923968
2016	392.936014	153.482446	112.086585	108.743551	21.6712245	28.8142151	43.8627725	16.0900736	23.4585142	57.9927492
2017	407.584139	144.489636	123.525813	108.202739	25.2375144	30.3483865	50.6806259	19.8240543	1.64443257	25.0090786
2018	430.058788	158.136916	109.622821	114.890994	21.1221376	32.7338199	55.4214662	20.3040459
Ptest	Region 1		Region 2		Region 3		Region 4		Region 5
	SHC	GP	SHC	GP	SHC	GP	SHC	GP	SHC	GP
2011	0.5%	0.8%	0.6%	0.6%	0.2%	0.5%	1.2%	0.9%	0.2%	0.2%
2012	0.4%	0.7%	0.5%	0.5%	0.2%	0.3%	0.7%	0.8%	0.2%	0.1%
2013	0.3%	0.7%	0.5%	0.5%	0.2%	0.2%	0.5%	0.6%	0.1%	0.1%
2014	0.3%	0.7%	0.3%	0.5%	0.4%	0.7%	1.0%	0.8%	0.2%	0.1%
2015	0.3%	0.7%	0.8%	0.6%		0.4%	2.0%	1.8%	0.4%	0.1%
2016	0.3%	0.6%	0.7%	0.5%	0.0%	0.4%	1.1%	2.0%	0.6%	0.1%
2017	0.3%	0.5%	0.4%	0.5%	0.4%	0.2%	1.3%	1.2%	0.0%	0.3%
2018	0.3%	0.4%	0.4%	0.5%	1.0%	0.3%	1.1%	1.1%

 

[mole999]

is the second axis configured as a stacked graph

 

[sajibo]

In Peltiers method, it's configured as an XY scatter, which is also what i did in my first image in the original post (but without using an offset like Peltier does). In the second image it's 2 sets of grouped columns on a primary and secondary axis. Is that what you meant?