cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ozpeacenik Frequent Visitor
Frequent Visitor

Wrong Sum totals when using a SUM measure from two different Tables

I have a table of Expense totals by month and another table of individual Sales.

 

I have managed to link them using a key column of ProductYearMonth which is unique to the Expense table.

 

I can create a matrix with the Columns being Year Month and the rows being product and display Total Revenue and Expense by Product and Period, but I can't work how to calculate Surplus (Revenue-Expense) and ROI (Revenue divided by Expense).  I have tried creating a measure but nothing I try is acceptable as a DAX formula.

 

Thank you for any help you can give me.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
ozpeacenik Frequent Visitor
Frequent Visitor

Re: Wrong Sum totals when using a SUM measure from two different Tables

I have solved this problem.  I created a column for Revenue and then all my calcs worked.

 

Revenue =
CALCULATE (
SUM ( 'Gift Data'[Gift Amount] ),
FILTER (
ALL ( 'Gift Data'[Gift Date] ),
'Gift Data'[Gift Date] <= MAX ( 'Gift Data'[Gift Date] )
)
)

1 REPLY 1
Highlighted
ozpeacenik Frequent Visitor
Frequent Visitor

Re: Wrong Sum totals when using a SUM measure from two different Tables

I have solved this problem.  I created a column for Revenue and then all my calcs worked.

 

Revenue =
CALCULATE (
SUM ( 'Gift Data'[Gift Amount] ),
FILTER (
ALL ( 'Gift Data'[Gift Date] ),
'Gift Data'[Gift Date] <= MAX ( 'Gift Data'[Gift Date] )
)
)