Sum values which are not in another table

Question

Hello,&nbsp;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 words sales table contains all ids and incorrect payments has some ids from sales). I want to create sum of amounts from sales table, with excluded payments from incorrect payments table.&nbsp;I know that I could make join in powerquery, but I would like to use measure instead. I have tried something like below, but it didn't work. Any ideas?&nbsp;CALCULATE(SUM('sales'[amount]),ISBLANK('incorrect payments'[Id]))&nbsp;Thank you

anonymous · Accepted Answer

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.SalesSales IDAmount1$502$1003$1504$1005$2506$1007$1508$1109$12510$108&nbsp;Incorrect PaymentSales IDReason4Declined Payment8Invalid Payment Method9Declined Payment10Duplicate Payment&nbsp;&nbsp;&nbsp;&nbsp;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]
    )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

Forum Discussion

Sum values which are not in another table

2 Replies

Related content

lookup value in another table which matches value in another

find max value based on another filtered column

How to replace one column value with another column value from another table?

lookup value in another column

Replace value with value from another column?

Sales ID	Amount
1	$50
2	$100
3	$150
4	$100
5	$250
6	$100
7	$150
8	$110
9	$125
10	$108

Sales ID	Reason
4	Declined Payment
8	Invalid Payment Method
9	Declined Payment
10	Duplicate Payment