5 Replies Latest reply on Nov 16, 2016 1:51 PM by Joe Oppelt

# Calculated Field from two Tableau Sheets (not two data sources)?

Hi, I'm not sure how to ask this question so I'm having a hard time finding an answer through various searches.

I have two tableau sheets.  Lets call one Profit and the other Expenses.  Each sheet has a list of Departments (the exact same list) and a single number for the Profit or Expenses.  I would like to create a third sheet or a calculated field that basically gives me the Net.  Is this possible in Tableau?  Some threads I read lead me to believe it's not possible but this is a pretty basic thing to do so I'm feeling like I'm just not searching for the right thing.  BTW, I'm using this profit/expense example because it's something that is easily understood.  My real life situation is similar in that it is a count of an item at a starting point LESS a count of an item at the end point for each department and these numbers are calculated using a CNTD of a uniqueID.

Sheet 1: Profit

North   \$100

South  \$200

East    \$300

West  \$400

Sheet 2: Expenses

North \$50

South \$75

East \$150

West \$25

Can I create Sheet 3 to calculate the difference between the two sheets?

Sheet 3: Net

North: \$50

South: \$125

East: \$150

West: \$375

Thanks for leading me onto a path if possible!
~Kim

• ###### 1. Re: Calculated Field from two Tableau Sheets (not two data sources)?

You shouldn't really be thinking about Sheets, you should be thinking about Measures. Profit and Expenses are measures, both dimensioned by Depeartments (and probably some others as well).

In Tableau you simply create a Calculated Field that would be [Profit] - [Expenses]

then when you use that in any capacity in Tableau, it subtracts the Expenses value from the Profit value - this could be a third sheet, or you could put all three on the same sheet.

For your real-life example it could be much the same - check that the dimensions of both measures are the same (or similar, depends on what you want to achieve) and then create a calculated field.  Test it to make sure it's working how you expect, and you're done

• ###### 2. Re: Calculated Field from two Tableau Sheets (not two data sources)?

Kimber,

It's quite difficult understand what do you mean by "Tableau Sheet".

Tableau is different from Excel and does not have a concept of Sheets.

If you can attach sample packagd workbook, that helps us to understand your issue.

Thanks,

Shin

• ###### 3. Re: Calculated Field from two Tableau Sheets (not two data sources)?

Kim,

Will this help you?

Here is what I did:

• ###### 4. Re: Calculated Field from two Tableau Sheets (not two data sources)?

Unfortunately I cant upload the workbook because it is sensitive information.  Let me ask this question differently to hopefully add another thought that may help.  I have measure that is a count of a uniqueID and the dimensions are hiring department and current department and this information is in every persons record (row).  Think of it as Susie started work in Department North and now works in Department South. Jane started in Department West but now works for Department North.  Susie and Jane's records both have dimensions hiring department and current department.  I can make a table that shows how many people started in each department when they were hired and I can make a table of how many people are currently in each department with simple counts in a table. Is there a way to do a subtotal in a regular calculated field.  Something like North.Hired - North.Current?

The data set is large-ish (75K rows) and the departments are numerous so it's not practical come up with all the combinations of people switching departments using IF statements.

Thanks for keeping the conversation going.  I hope I didn't confuse things with an alternate example.

~Kim

• ###### 5. Re: Calculated Field from two Tableau Sheets (not two data sources)?

Hopefully the hiring department and current department are 2 different dimensions, then you can do [Hiring Department} <> [Current Department]

Without publishing your REAL data, maybe some sample dummy data would help.  Make it the same dimensions that your real data has, but the values should be fictitious

• ###### 6. Re: Calculated Field from two Tableau Sheets (not two data sources)?

Kimber McDevitt wrote:

Unfortunately I cant upload the workbook because it is sensitive information. ...

You can anonymize your workbook:

The workbook will be helpful here.  You'll need to do different things if the numbers on your respective sheets are from table calcs, sums of measures, or values from aggregate calcs.

But the bottom line is that you need to derive the values within the sheet where you want the math to be done.