Forum Discussion
Measure or Formating to calculate the difference between 2 columns on 2 tables but share same codes
Hey! Ive been wracking my head on this and trying to find some help. So I have a 2 reports that pull directly out of our accounting program and the 2 tables are for sales invoices and sales credits.
I am trying to find a way to create a single table that has it calculated but it always results in some strange numbers that dont match.
The tables do share the same items codes and want to calculate the total difference when selecting different filters for reporting purposes.
The Outcome is I want a table that looks like below but im not sure what im missing to match it.
Hi Marcus-G ,
As per your description, I have created two tables.
You can refer to the following steps.
1. create relationship for both tables.
2.Create measure and calculate the sum separately.sumCredit = CALCULATE(SUM(SalesCredits[Amount]),FILTER(ALL(SalesCredits),'SalesCredits'[ItemCode] = MAX('SalesCredits'[ItemCode])))sumInvoice = CALCULATE(SUM(SalesInvoices[Amount]),FILTER(ALL(SalesInvoices),'SalesInvoices'[ItemCode] = MAX('SalesInvoices'[ItemCode])))3. Create the measure and calculate the difference.
diff = [sumInvoice] - [sumCredit]If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Please provide your source data (or at least a representative sample of it) - I can't give you a solution without the source data to work with.
Please provide a file or a table pasted in here, not an image.
Regards
Phil
Hi Marcus-G ,
As per your description, I have created two tables.
You can refer to the following steps.
1. create relationship for both tables.
2.Create measure and calculate the sum separately.sumCredit = CALCULATE(SUM(SalesCredits[Amount]),FILTER(ALL(SalesCredits),'SalesCredits'[ItemCode] = MAX('SalesCredits'[ItemCode])))sumInvoice = CALCULATE(SUM(SalesInvoices[Amount]),FILTER(ALL(SalesInvoices),'SalesInvoices'[ItemCode] = MAX('SalesInvoices'[ItemCode])))3. Create the measure and calculate the difference.
diff = [sumInvoice] - [sumCredit]If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
