Synchronizing Axes Across Worksheets
Ever wanted to combine different graphs together, but even a Measure Values field combined with another into a dual axis is still not good enough? I’m not going to insult you by assuming you haven’t thought of using floating objects; but I do know from personal experience that getting the axes of different charts to sync up has proven more complicated than it needed to be in the past.
In this article, I’ll explain a simple approach to ensuring your axes align perfectly with each other. Of course, you can also watch the video here if the thought of reading another article triggers instant procrastination.
Assuming you’ve built your views and have the two charts ready to overlay on top of one another, the next step is to create a new calculation. The trick is to use the MAX() function here. This function compares two values and returns whichever is the highest. The caveat is that it can only compare two values at a time. However, you can nest MAX() functions inside of each other as a workaround. See the example below using Superstore data:
If you ignore the glaringly obvious fact that the sum of ‘Quantity’ will most likely always be higher, you should still see the point I’m trying to make.
By applying this calculation as an invisible reference line on both worksheets, the axes will always be synced because they will both go as high as the maximum value between them.
Now when it comes to laying one chart over the other, you’ll obviously need to remove any background colour from the chart that floats on top, so that you can actually see the chart underneath.
In addition to your width, height, and x and y positions, make sure the padding settings match as well, as floating objects usually default to have 0 padding, whereas non-floating objects default to a padding value of 4. This should help improve alignment, but it still may not be perfect.
Another item to look at is the width of the axis on the top chart and whether it matches the axis of the chart underneath (see image below).
When that’s all said and done, if alignment still isn’t great, you may simply need to format any axes of the chart underneath to appear invisible, and rely on the text and tick marks of the top chart’s axis to guide the user.
If the titles also clash slightly, you can either repeat the process of making the title underneath appear invisible, or you can replace it with a single blank space. I say “blank space” and not simply “remove it” because you still want the title’s height to be the same between charts, and if you remove it completely, well, obviously there won’t be a title. Alternatively, you can remove both titles and add a text object to the dashboard to serve as the title instead.
Now make sure to show the legends for both charts. When bringing in the legend for the floating chart, the legend will likely default to a floating object as well. By shift-click dragging the legend over a container, it should snap into place. If you position it beneath the first legend, hide the title and remove some of the padding settings on the sides of both legends, where they meet each other, you can imitate the look and feel of a single, combined legend.
