I am trying to plot revenue and costs on a column chart, along with a line for profit (black line) and a line for ROI (blue line). All series will be on the left Y axis, except for ROI, which will be on the right Y axis since it is the only percentage. My graph looks pretty good, except that the way the Y axes are lining up is very confusing.
I expected my graph to have all my “values” series plotted against the left Y axis, and then have my one “percent” series (ROI) plotted against the right Y axis, with the 100% (i.e., breakeven) lining up with 0 on the left axis (i.e, where Profit = $0, ROI = 100%). However, the two Y axes do not align, which makes the graph very confusing. It looks like when I am at 100% breakeven, I am losing money, because the 100% on the right side lines up with -$2200 on the left side.
Ordinarily when you have 2 Y axes, you wouldn’t need/want to control where the axes values lines up because that’s the whole point of having 2 Y axes. In this case, because one of them is a percentage, it actually makes a lot of sense to have them align.
I have tried Pinning/Unpinning either/both axes from zero. I have tried creating a calculated field for ROI-1, so that ROI of 100% actually was 0. I have tried having 2 left axes instead. None of these work. I have also tried setting a min/max value for either/both Y axes, however, this would not be a solution because the graph needs to be dynamic over time.
Does anyone have any ideas to line up these values so the graph is a bit more sensible?