looking for bar chart format suggestions

badhorse

I'm trying to draw a chart to match a specification and need help figuring out how to do it. The requirement is a side-by-side bar chart (2 bars) with a line series (centered on the 2 bars) on top. Problem #1 is that the 2 bars have different scales...so I've tried doing this with a two Y axis chart (bar+bar) chart where the line series is plotted as the 2nd value on the first (Y1) axis. What I get, when I do this is, of course, the 2nd axes' bar overlaying the 1st' bar+line instead of beside the 1st axes' bar.

I'm considering either putting the all three values on the first axis (bar+line+bar), scaling the values for the 2nd bar appropriately dynamically based on the max value, and putting the 2nd bar's data values on the 2nd axis as well so I get the appropriate 2nd Y axis labeling. This has (at least) two problems in that it requires turning visibility off on the 2nd Y axis' series bars (which I don't see how to do) and that there's a bar's-width space allocated for the line series in between the bars where it's desired there be none (any way to make this smaller)?

Is there another way to pull this off?

badhorse

Another thing we've tried, is adding an additional "empty" series on the 2nd Y axis as series 1, and moving the current series to series 2. This has the desired effect of splitting the width of the bar on the 2nd Y axis in half and putting it next to the series 1 bar from the 1st Y axis just as we'd like it. But, when we turn off the visibility of the "empty" series (series 1 on Y2) to remove it from the legend and hide it completely, the series 2 bar reverts back to the full width of both series. Quite frustrating since it's very close to what we need.