Forum Discussion
How to Do Subtraction & Addition Using Tables With No Direct Relationship Using DAX?
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 ...
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])
Hi Greg_Deckler ,
I am not sure, I am following it.
Would you mind writing the whole calculation?
Thanks,
Mohammad
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_DueThat 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
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.