Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
dferry
Helper I
Helper I

Subtract one column from another based on criteria from another table

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_0-1650214671377.jpeg

 

1 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 )
)

View solution in original post

9 REPLIES 9
dferry
Helper I
Helper I

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

 

@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" }
    )
)

 

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

dferry_2-1650297621213.png

 

@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 )
)

You are a genius.  Thank you very much.

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

dferry_0-1650296517751.png

 

dferry_1-1650297180556.png

 

@dferry 

Are you getting correct numbers?

tamerj1
Super User
Super User

Hi @dferry 

how does you report look like? 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.