Subtract one column from another based on criteria from another table

[dferry]

I have two tables. One table has debits and credits and the other table has analytic codes.

I need to subtract the debit - credits but only using certain analytic codes from another table.

For example i have a GL account of 30010 that when i subtract the debits and credits it gives me a total but  for all of the analytic codes in another table.  I want to be able to choose (filter) what analytic codes should be included in the calculation.

[dferry]

GL Account          Revenue Credits           Revenue Debits        Revenue

30010                  5,000                              4,000                         1,000

40010                  1,000                              2,000                         -1,000

50010                  500                                     250                          250

The GL accounts are in the GTR table.  For each GL Account there are analytic codes in the GTS table.  Each analytic code is represents a manufacturer.  I have to add up all the credits and debits then subtract the debits from the credits to get Actual Revenue.  Credits - Debits = Revenue.  

I created one measure for calculating the Credits and one measure for the Debits.  Then i created a third measure

to subtract the two to get my Revenue. Credits - Debits = Revenue.

This is the measure for calulating the Credits.

NY-CreditRev30010 = CALCULATE(Sum(gtr[gtr_credit]), (gtr[gla_id] = "30010")filter(gts[gla_id_anal]), gts[gla_id_anal] IN "0100101","0200101","0300101","0400101" )

I get an error saying The syntax for 'filter' is incorrect.

Thanks

[tamerj1]

@dferry 

Please try this and share a screenshot of the report table/visual

NY-CreditRev30010 =
CALCULATE (
    SUM ( gtr[gtr_credit] ),
    gtr[gla_id] = "30010",
    FILTER (
        gts[gla_id_anal],
        gts[gla_id_anal] IN { "0100101", "0200101", "0300101", "0400101" }
    )
)

[dferry]

Sorry I messed up your measure.  Screen shot below is your measure with the correct analytics.

[tamerj1 · Accepted solution]

@dferry 

I think you need to CROSSFILTER the relationship. please use the correct analytics in the following 

NY-CreditRev30010 =
CALCULATE (
    SUM ( gtr[gtr_credit] ),
    gtr[gla_id] = "30010",
    FILTER (
        gts,
        gts[gla_id_anal] IN { "0100101", "0200101", "0300101", "0400101" }
    ),
    CROSSFILTER ( gtr[gtr_id], gts[gtr_id], BOTH )
)

[dferry]

You are a genius.  Thank you very much.

[dferry]

I had a couple wrong analytics in the measure i sent to and changed them.  

[dferry]

[tamerj1]

@dferry 

Are you getting correct numbers?

[tamerj1]

Hi @dferry 

how does you report look like?