9 Replies Latest reply on Nov 12, 2019 10:15 AM by Jeremy Locanas

# Why does subtracting a Fixed calculation from another Fixed calculation double the subtraction?

I have a data source linking Bills with Invoices by Invoice #.

I have two fixed calculation columns:

1) InvoiceRevenue = { FIXED [Invoice #],[Revenue] : min([Revenue]) }

and

2) BillsCost = { FIXED [Bill #],[Total PS Cost] : min([Total PS Cost]) }

both designed to remove duplicate entries from the join.

I then Sum the costs using

3) BillsTotal = { fixed [Invoice #] : sum([BillsCost]) }

Everything works fine and perfectly when these 3 fields are separate.

But when I create a new column

4) TrueGP = InvoiceRevenue - BillsTotal

For some reason TrueGP keeps subtracting InvoiceRevenue - (BillsTotal * # of duplicate Revenue lines)

Can someone help me understand why this is happening?

Thanks!

--

• ###### 1. Re: Why does subtracting a Fixed calculation from another Fixed calculation double the subtraction?

Jeremy,

I'm not sure, but I'm wondering if having [Revenue] and [Total PS Cost] in

the dimensions part of the LODs might be causing the duplications.

Maybe they should just be:

InvoiceRevenue :  {FIXED [Invoice#]:MIN(Revenue)}

and BillsCost : {FIXED [Bill#]:MIN(Total PS Cost)}

1 of 1 people found this helpful
• ###### 2. Re: Why does subtracting a Fixed calculation from another Fixed calculation double the subtraction?

Hi Swaroop - thanks for the reply.  I tried that initially but it didn't produce the correct answers individually.

IE I have Invoice INV132913 with two revenue values \$0 and \$15,400.

That has 4 bills, 2 both with the id 633-483 and values of \$2632.84 and \$2632.83,

then there are 2 other bills 101772 for \$5214.29 and 633-487 for \$5134.62.

If I try it your way I end up with \$0 for both all 3 revenue lines and then for bills I get row values of \$2632.83, \$5214.29, and \$5134.62.  Tableau auto-combines 633-483 into one row b/c it's a duplicate.

If I do it my way, I get all three correct values of \$15400 for each revenue line, and \$5266 for the combined 633-483, \$5135, and \$5214 but the duplication occurs when combining as mentioned...

• ###### 3. Re: Why does subtracting a Fixed calculation from another Fixed calculation double the subtraction?

Jeremy,

Thanks for the workbook, I think I see it better now.

I think you were on the right track,

maybe just need to add a second level of nested LOD:

InvoiceRevenue2:

{ FIXED [Invoice]:SUM(

{ FIXED [Invoice],[Revenue]:MAX([Revenue])}

)}

BillsTotal2

{ FIXED [Invoice]:SUM(

{ FIXED [Invoice],[Bill],[Cost]:MAX([Cost])}

)}

to see if that is closer. 1 of 1 people found this helpful
• ###### 4. Re: Why does subtracting a Fixed calculation from another Fixed calculation double the subtraction?

Hi Swaroop - I think this worked!  I'm checking over as many entries as possible first, but thank you very much!   Have a great weekend!

• ###### 5. Re: Why does subtracting a Fixed calculation from another Fixed calculation double the subtraction?

Jeremy,

I just realized there may be a snag in the situation where (not sure how often it would happen)

in the raw data for billing there are truly two costs for the same amount in the same bill:

Bill#             Cost

666-485       \$1044

666-485       \$1044

Because with the above calculated fields, this will only get counted once.

Is there another identifier like time of cost that would distinguish these two?

• ###### 6. Re: Why does subtracting a Fixed calculation from another Fixed calculation double the subtraction?

Hi Swaroop - you are correct.  I found 1 instance like that out of 30K rows so far out of 200K rows total.  There is a location field that could be used, but I'm not 100% that would work.  Where would you recommend inserting that [Location] at?

Thanks again!

• ###### 7. Re: Why does subtracting a Fixed calculation from another Fixed calculation double the subtraction?

Hi Swaroop - Location won't work as a unique identifier, sorry.  I just found a 2nd example where all fields tie including location.  Is there a way to create a new column to simply flag if there are duplicate Bill entries?  I think that would suffice, as I don't think there is a way to account for 100% coverage considering Tableau's logic restrictions.  If we have a flag, we can at least exclude those line items for double-checking separately...  Thanks!!!

• ###### 8. Re: Why does subtracting a Fixed calculation from another Fixed calculation double the subtraction?

Jeremy,

I think the flagging method could work.

I made up a dataset with duplicates and tried this,

which I think is similar to the way you started:

[Flag Duplicate Item]:

IF { FIXED [Invoice],[Revenue],[Bill],[Cost]:SUM(1)}>1

THEN "Duplicate"

END

[Flag Invoice with Duplicate]:

{ FIXED [Invoice]:MAX([Flag Duplicate Item])} 