1 2 Previous Next 17 Replies Latest reply on Oct 25, 2018 8:10 AM by soni s

# How to get same scale on x and y axis for scatter plot

I am creating a scatter plot with two measures. I would like the X and Y axes to have the same scale. Would like them both to start at zero, and extend to the maximum of the higher measure.

Bonus Question: Is there a way to add a 45 degree reference line on the scatter plot?

• ###### 1. Re: How to get same scale on x and y axis for scatter plot

Kathy,

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.

Regards,

Joshua

• ###### 2. Re: How to get same scale on x and y axis for scatter plot

Thanks Joshua.

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.

• ###### 3. Re: How to get same scale on x and y axis for scatter plot

Kathy,

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!

Regards,

Joshua

• ###### 4. Re: How to get same scale on x and y axis for scatter plot

Good and flexible technique Joshua.

Ramon

• ###### 5. Re: How to get same scale on x and y axis for scatter plot

Thanks Ramon!

• ###### 6. Re: Re: How to get same scale on x and y axis for scatter plot

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.

1 of 1 people found this helpful
• ###### 7. Re: Re: How to get same scale on x and y axis for scatter plot

Also, there's an idea to support angled reference lines:

http://community.tableau.com/ideas/1203

• ###### 8. Re: How to get same scale on x and y axis for scatter plot

Hi Jonathan,

Excellent solution.

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.

Best,

Ramon

• ###### 9. Re: How to get same scale on x and y axis for scatter plot

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.

• ###### 10. Re: How to get same scale on x and y axis for scatter plot

Thanks Jonathan. I get it.

Ramon

• ###### 11. Re: How to get same scale on x and y axis for scatter plot

Jonathan,

There's nothing better than when complexity can be simplified.  I love it!

Regards,

Joshua

• ###### 12. Re: How to get same scale on x and y axis for scatter plot

Joshua,

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.

Regards,

Kathy

• ###### 13. Re: How to get same scale on x and y axis for scatter plot

Thanks Kathy!

Definitely have a look a Jonathan's reply as he greatly simplifies the approach.

Regards,

Joshua

• ###### 14. Re: Re: How to get same scale on x and y axis for scatter plot

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.

Kevin

1 2 Previous Next