Forum Discussion
Solved
Sum values which are not in another table
Hello, I have datamodel with two tables - table with sales (id, amount) and second table with incorrect payments (id, reason). Tables are connected by relationship on Id in both tables (in other ...
I made 2 dummy tables to replicate the story you have provided. This essentially lets you search sum the Sales[Amount] by filtering out rows where the Sales ID exists in the Incorrect Payments table.
Sales
Sales ID Amount 1
$50 2 $100 3 $150 4 $100 5 $250 6 $100 7 $150 8 $110 9 $125 10 $108 Incorrect Payment
Sales ID Reason 4 Declined Payment 8 Invalid Payment Method 9 Declined Payment 10 Duplicate Payment Sales Excluding Failed Payment = VAR _VariableTable = CALCULATETABLE ( VALUES ( 'Incorrect Payments'[Sales ID] ), ALLSELECTED ( 'Incorrect Payments' ) ) RETURN SUMX ( FILTER ( ALLSELECTED ( Sales ), NOT ( Sales[Sales ID] ) IN _VariableTable ), Sales[Amount] )
I made 2 dummy tables to replicate the story you have provided. This essentially lets you search sum the Sales[Amount] by filtering out rows where the Sales ID exists in the Incorrect Payments table.
Sales
| Sales ID | Amount |
1 | $50 |
| 2 | $100 |
| 3 | $150 |
| 4 | $100 |
| 5 | $250 |
| 6 | $100 |
| 7 | $150 |
| 8 | $110 |
| 9 | $125 |
| 10 | $108 |
Incorrect Payment
| Sales ID | Reason |
| 4 | Declined Payment |
| 8 | Invalid Payment Method |
| 9 | Declined Payment |
| 10 | Duplicate Payment |
Sales Excluding Failed Payment =
VAR _VariableTable =
CALCULATETABLE (
VALUES ( 'Incorrect Payments'[Sales ID] ),
ALLSELECTED ( 'Incorrect Payments' )
)
RETURN
SUMX (
FILTER ( ALLSELECTED ( Sales ), NOT ( Sales[Sales ID] ) IN _VariableTable ),
Sales[Amount]
)
That's exactly what I needed! Thank you so much!
