3 Replies Latest reply on Feb 13, 2019 1:47 PM by Jim Dehner

# Calling all Tableau Experts - Trying to subtract sumproduct totals from each other.

Question:

Can someone help me subtract my assets vs liabilities in my "totals" worksheet (attached)?

I have created a weighted average type of report; I was able to finish the report with all the correct numbers ("full report" worksheet), however, I'm stuck trying to subtract my grand totals, and it has to do with the fact that I'm doing complex calculations to mimic excels sum-product function.

Report background: In short, different rows in my report use different calculations, and then I must sum them up using their original calculations aka the sumproduct feature in excel. I do these calculations in the 'Yield Final' calculated field.

Below is a screenshot of the completed correct report, with the focus being on the Percentage column. (I've also attached the work book - refer to "Full Report" worksheet):

(Note: I scrambled/encrypted all the data: text and numbers; so I apologize if the report really doesn't make sense.)

From this full report, I created another worksheet called "Totals" which is basically a copy of the full report, with some exclusion and hiding of certain rows.

It looks like this:

All I'm trying to do, is be able to subtract the Assets and liabilities from each other in the totals worksheet; in this specific case it will be 1.23%-.33% result should be .9%.

I'm struggling with this due to the way I created my calculations.

I've tried various things such as the lookup() function with no avail.

Any help would greatly be appreciated!

• ###### 1. Re: Calling all Tableau Experts - Trying to subtract sumproduct totals from each other.

Hi Mike

I am not an account but I did note this

Total is a table calculation and the table calculations are at the bottom of the order of operation after Grand Total

Suggest you replace Total (sum(avg bal)) with {fixed:sum(avg bal))

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Calling all Tableau Experts - Trying to subtract sumproduct totals from each other.

Hello Jim,

The Yield final calculation is actually correct as it functions properly for the report; not sure doing this will help solve my issues of trying to subtract Assets vs Liabilities in the Total worksheet.

I need to figure out how to create a new calculated filed possibly and set some kind of tableau logic to subtract the two values from each other.... hope that makes sense.

• ###### 3. Re: Calling all Tableau Experts - Trying to subtract sumproduct totals from each other.

good luck

sorry didn't explain the goal here

the suggestion was to get you away from a table calculation with an LOD that would return the same values - once you are away from table calculations yo can us Grand Totals

Jim