topic Re: SUMX help to use SUMX where rows from two tables must be subtracted. in DAX Commands and Tips

SUMX help to use SUMX where rows from two tables must be subtracted.

daneilroy — Thu, 10 Mar 2022 09:38:04 GMT

Hello everyone

I'm having trouble solving this problem.

I have a Transaction table and an item table.

In my Transaktion tabel i have a lot og columns where i wanna use the 'transaktion amount' and 'transaktion key'

In the Items table i wanna use the 'transaktion key' and the 'item registration fee'

I want to sum 'transaction amout', for the whole transaktion table, but where the transaktion wich is registered i the items table as a "court_fee or 'registration' are deducted.

So if a transaktion is a court_fee og a registration they should NOT be a part of the sum of the transaktion amount.

I sorted so. both tables are only march 2022 - Where the first photo is the items table and the second is the transaktion table.

Re: SUMX help to use SUMX where rows from two tables must be subtracted.

johnt75 — Thu, 10 Mar 2022 11:01:39 GMT

Transaction Amount Measure = SUMX( 'transaction table',
var itemFees = CALCULATE( SUMX( RELATEDTABLE( 'items' ), 'items'[item registration fee]),
    NOT( 'items'[item type] IN { "court fee", "registration fee" } )
return itemFees + 'transaction table'[transaction amount]
)

Re: SUMX help to use SUMX where rows from two tables must be subtracted.

tamerj1 — Thu, 10 Mar 2022 11:27:23 GMT

daneilroy
Would you please share a screenshot of your data model?

Re: SUMX help to use SUMX where rows from two tables must be subtracted.

daneilroy — Thu, 10 Mar 2022 14:27:26 GMT

Hello John!

Thank you very much! it worked! i just had to do some formating but everything else is perfect.