Forum Discussion
Lookup a summed value from different table
Hi, I'm trying to do a very simple calculation but haven't been able to get it working.
i got 2 tables, Leadsheet and JE. Leadsheet has 1 to many relationship with JE table.
Leadsheet
JE
I want a DAX for a new column in leadsheet that shows the SUM OF JE = CALCULATE THE SUM OF THE AMOUNT COLUMN IN JE TABLE FOR EACH ACCT NUMBER in LEADSHEET.
thanks
Hi moezali
On the Leadsheet Table you could add this column
New Column = CALCULATE(SUM('JE'[Amount]))or you could do this as a measure which might play better with other filters (Try both)
New Measure = CALCULATE(sum(JE[Amount]) )
In this scenario, since you may have multiple ACCT Number in JE table, it's not a one-to-one mapping so that we can't use LOOKUPVALUE() function. For your requirement, you just need build relationship between two tables on ACCT Number column. Then add a column like "=RELATED(JE[Amount])" in LeadSheet table.
Regards,
2 Replies
Hi moezali
On the Leadsheet Table you could add this column
New Column = CALCULATE(SUM('JE'[Amount]))or you could do this as a measure which might play better with other filters (Try both)
New Measure = CALCULATE(sum(JE[Amount]) )
In this scenario, since you may have multiple ACCT Number in JE table, it's not a one-to-one mapping so that we can't use LOOKUPVALUE() function. For your requirement, you just need build relationship between two tables on ACCT Number column. Then add a column like "=RELATED(JE[Amount])" in LeadSheet table.
Regards,