8 Replies Latest reply on Feb 19, 2016 11:44 AM by Rody Zakovich

# How to create a Scatter Plot based on same calculation but different dimensions

Hi all,

I am fairly new to Tableau and still learning the software. I have the following scenario that I am trying to display on a scatter plot, but having difficulty returning a desired result.

Scenario:

I have created a calculated field that computes 3 Year % Change:

(SUM([FY2015]) / ((SUM([FY2014])+SUM([FY2013])+SUM([FY2012]))/3))-1

I want to display a scatter plot using the above formula but for two separate sectors:

Y-axis: 3YR %Change in Sector A funding

X-axis: 3YR %Change in Sector B funding

For the data values, I will be using Departments.

Desired Result:

I want to scatter plot Departments to show their 3YR % change in Sector A funding relative to Sector B. For example, in Department 1, the 3YR %Change is 2% in Sector A and -5% in Sector B, and plot all of the departments to show this kind of results.

Problem:

I am not sure how to code it in properly. I have tried creating other calculated fields to make the calculation relative only to Sector A or B, but when I put Departments into scatter plot, the result is zero. But what I put a Sectors dimension in, I get values for Sector A and Sector B.

Thank you!

• ###### 1. Re: How to create a Scatter Plot based on same calculation but different dimensions

hi Dmitriy,

It's difficult to give you an exact solution without seeing the 'shape' of your data...However I think this will help.

So the calculation you have is an AGGREGATE calculation. As you have the SUM() wrapping your values. As such the result is dependent on the VizLoD. For example, thinking Superstore data set, if you had an AGGREGATED measure, SUM([Sales]), and brought this into your Viz, it would show the total sales for everything, if you then bring in Region you then get 3 values for each region. So in this way the result isn't calculated until you use it...However row level calculations are run 'Off Canvas' (as I've heard it called and really like the term). So we can put the Row Level part within your formula. One for Sector A and one for Sector B, and then these use these fields in your Row and Column Shelf and then control the 'Grain' (number of points) by dragging the appropriate dimension onto the detail shelf.

So you'd have

[Sector A Change]

(SUM(IIF([Sector] = 'A', [FY2015], NULL) )/ ((SUM(IIF([Sector] = 'A',[FY2014],NULL))+SUM(IIF([Sector] = 'A', [FY2013],NULL))+SUM(IIF([Sector] = 'A', [FY2012],NULL)))/3))-1

and for sector B

[Sector B Change]

(SUM(IIF([Sector] = 'B', [FY2015], NULL) )/ ((SUM(IIF([Sector] = 'B',[FY2014],NULL))+SUM(IIF([Sector] = 'B', [FY2013],NULL))+SUM(IIF([Sector] = 'B', [FY2012],NULL)))/3))-1

you may need to double check the brackets as I've not used the Calculation Editor (which has made me pretty lazy as it tells me where I've missed one!!)

Hope this does the trick, but please post back if not (ideally with a mock up of your situation)

1 of 1 people found this helpful
• ###### 2. Re: How to create a Scatter Plot based on same calculation but different dimensions

Hi Dmitriy,

First off, welcome to the community! You're gonna love it here, I promise!

This is totally possible in Tableau, but how we do this can depend on how your data is structured, and what the overall end goal is.

If possible, can you provide an example workbook? This will give us the ability to demonstrate a solution that best fits your data structure and criteria.

http://community.tableau.com/docs/DOC-1251

If you have sensitive data, than you can create an anonymized version of it using this guide

Best regards,

Rody

2 of 2 people found this helpful
• ###### 3. Re: How to create a Scatter Plot based on same calculation but different dimensions

It worked perfectly!

How come did IIF was able to do the trick?

I have attached a sample workbook to illustrate what I intended to do and it worked well both in the attached example and my actual data.

Thank you!

• ###### 4. Re: How to create a Scatter Plot based on same calculation but different dimensions

Excellent news!...so by way of an explanation I've attached a simple mock up in Excel

So the IIF part is being computed at a Row Level, as per the 2 IF statements in the Excel. But then we are asking it to AGGREGATE (by way of a SUM) these results and return us that, which is the SUM of the 2 columns (equivalent to your 2 measures) shown below the data. Then like the Region/Superstore example I gave earlier the actual value these take is dependent on the Level of Detail of the Viz (known as VizLoD), you are choosing the VizLoD via the detail shelf (and also the colour, shape, size)

Hope that makes sense

2 of 2 people found this helpful
• ###### 5. Re: How to create a Scatter Plot based on same calculation but different dimensions

I see, this makes a lot more sense now. Thank you for the help!

• ###### 6. Re: How to create a Scatter Plot based on same calculation but different dimensions

Hey Simon Runc

Like the "Off Canvas" reference!

This is a great example showing how/why this happens.

Regards,

Rody

• ###### 7. Re: How to create a Scatter Plot based on same calculation but different dimensions

Cheers mate...should have given credit to the inventor!!

but LOVE the term...it's multiplied, many times, my ability to explain the differences between calculation types in the training I give...In fact it's the starting point of the calculations section...just 2 simple words, and people go from to !!

• ###### 8. Re: How to create a Scatter Plot based on same calculation but different dimensions

That's awesome! Well I'm glad that it has helped you out!