How to Do Subtraction & Addition Using Tables With No Direct Relationship Using DAX?

[Mohammadwazeri]

Hi Guys,

I want to create a measure to subtract and add the columns from the tables with no direct relationship, see the screenshot below.

After some search on the internet, I created the below measure, BUT it doesn't work properly when I use it with the column 'Sales Invoice'[Aging Bucket]. It shows the $ values way too big.

Balance Due = 

VAR __IN_Invoice_Amnt = SUM('Sales Invoice'[Ivoice Amnt])

VAR __IN_Balance = SUM('Incoming Payment'[IN_Balance])

VAR __CN_Balance = SUM('Credit Memo'[CM_Balance])

VAR __DT_Balance = SUM('Down Payment'[DP_Balance])

RETURN

    ((__IN_Balance_Due - __RC_OpenBalSc) - __CN_Open_Balance) + __DT_Balance_Due

Can some one please give me some light on this?

Thank you,

Mohammad

[Greg_Deckler]

@Mohammadwazeri I guess first question would be why you can't create those relationships. But, you should be able to do something like this:

VAR __PBCustomers = DISTINCT('Customer'[PBCustomerKey])

VAR __Invoices = SUMX(FILTER('Sales Invoice'), [PBCustomerKey] IN __PBCustomers),[Invoice Amt])

[Mohammadwazeri]

Hi @Greg_Deckler ,

I am not sure, I am following it. 

Would you mind writing the whole calculation?

Thanks,

Mohammad

[Greg_Deckler]

@Mohammadwazeri 

Balance Due = 
VAR PBCustomers = DISTINCT('Customer'[PBCustomerKey])

VAR Customers = DISTINCT('Customer'[Customer_Key])

VAR IN_Invoice_Amt = SUMX(FILTER('Sales Invoice'), [PBCustomerKey] IN PBCustomers),[Invoice Amt])

VAR IN_Balance = SUMX(FILTER('Incoming Payment', [Customer_Key] IN Customers), [IN_Balance])

VAR CN_Balance = SUMX(FILTER('Credit Memo', [PBCustomerKey] IN PBCustomers),[CM_Balance])

VAR DT_Balance = SUMX(FILTER('Down Payment', [PBCustomerKey] IN PBCustomers),[DP_Balance])

RETURN

    ((IN_Balance_Due - RC_OpenBalSc) - CN_Open_Balance) + DT_Balance_Due

That said, this line in your code doesn't seem to correspond to known VARs

((IN_Balance_Due - RC_OpenBalSc) - CN_Open_Balance) + DT_Balance_Due

[Mohammadwazeri]

Thank you @Greg_Deckler  for your quick reply!

I tried to use the "Balance Due" with the 'Sales Invoice'[Aging Bucket], but now it is adding every values for every aging bucket this is the main problem.