What are the two measures and how are they related? I have some thoughts, but I'm not sure which direction to go without a little more detail.
The measures are Expense and Income. Both are in dollars.
The columns in my data are: Job ID, Income, Expense
Expenses range from 0 to $20,000 per Job ID.
Income ranges from 0 to $25,000 per Job ID.
I want to show Expense on the X axis and Income on the Y axis.
I would like both axes to range from 0 to $25,000 (or the max of the largest measure). I would like the tick marks to be at the same intervals.
What is happening is that when I filter on certain jobs, the axes go out of alignment.
Here's a possibility that may require some adjustment based on your exact situation. I'm using Profit and Sales as the two measure from the sample Superstore data provided with Tableau. In this example, I know Sales is always going to be larger, but it should work with your two measure.
I'll try to walk through my thinking:
1. Create a calculation that will give you the measure with the maximum value.
[Profit or Sales (max value)]
IF WINDOW_MAX(SUM([Profit])) > WINDOW_MAX(SUM([Sales])) THEN SUM(Profit) ELSE SUM([Sales]) END
Place this on both Rows and Columns to get a scatter plot with the axes having the same scale. You can also turn on the trend line. There's your 45 degree angle!
2. Create a corresponding field that gives you the other measure:
[Profit or Sales (other)]
IF WINDOW_MAX(SUM([Profit])) > WINDOW_MAX(SUM([Sales])) THEN SUM(Sales) ELSE SUM([Profit]) END
Place it on the Rows. Now you can use the marks card to do things like make the marks on the line small and transparent.
3. Now make the (other) field a dual axis. Synchronize the axes so the scales match. You can hide the header for the axis on the right.
Almost there! It's just a little hard to tell which axis is which, so a couple more calculations will give us the labels.
4. Create the Label calculations (they are almost identical to the original calcs). Place them on Rows and Columns.
Profit or Sales (max value) label
IF WINDOW_MAX(SUM([Profit])) > WINDOW_MAX(SUM([Sales])) THEN "Profit" ELSE "Sales" END
Profit or Sales (other) label
IF WINDOW_MAX(SUM([Profit])) > WINDOW_MAX(SUM([Sales])) THEN "Sales" ELSE "Profit" END
Edit the Axis and remove the existing label since you now have calculated ones. Here's the end result:
Hope that helps!
Scatter Plot - JM.twbx.zip 381.7 KB
Good and flexible technique Joshua.
1 of 1 people found this helpful
Nice work, Joshua! If you're just looking for same size axes, your technique gave me the idea for a simpler way. Set up the following two table calcs, this one for the min value:
IF WINDOW_MIN(SUM([Profit])) < WINDOW_MIN(SUM([Sales])) THEN WINDOW_MIN(SUM([Profit])) ELSE WINDOW_MIN(SUM([Sales])) END
And this one for the max value:
IF WINDOW_MAX(SUM([Profit])) > WINDOW_MAX(SUM([Sales])) THEN WINDOW_MAX(SUM([Profit])) ELSE WINDOW_MAX(SUM([Sales])) END
Then put the two calcs on the level of detail shelf, with the Compute Using of the dimension on the Level of Detail. Create either 4 reference lines (2 on each axis) or 2 reference bands (one on each axis) and you'll have the same axis lengths. Here I've used reference bands:
This takes advantage of the feature where Tableau will pin the axis length to at least the value of the largest/smallest mark or reference line. Then it's easy to hide the reference lines.
As for getting the diagonal line, I couldn't come up with an easy solution to get a nice 45 degree line, any ideas I had would've been as much work as yours was. I did one hack, using a background image:
Workbook is attached.
I have a question: Is it not enough to add a reference line in each axis with the formula for the max value?
I think that adding a ref line for the min value we have the risk of exclude zero from both axis in a particular case that min value is greater than zero.
To get the axes to automatically have the exact same scale they need to have the same min & max values, otherwise they look a little off. In the Show Ref Lines worksheet, if you take the minimums off and also filter out Office Machines, you'll see how the axes lose their sync.
0 won't be excluded because the default axis range always includes 0, I'd forgotten about mentioning that.
Thanks Jonathan. I get it.
There's nothing better than when complexity can be simplified. I love it!
Thank for your detailed explanation. I haven't been able to fully implement your ideas yet, but didn't want too much time to pass before acknowledging your help.
Thank you also to Jonathan and Ramon for contributing to the discussion.
Definitely have a look a Jonathan's reply as he greatly simplifies the approach.
Sorry I am late to the party here but I am looking to add a diagonal line as well and came across this thread. I believe you could just duplicate your x-axis and then add a dual axis and show the trend of the duplicated field then just make all the marks transparent and hide the dual axis header.