Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sebx
Regular Visitor

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 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.

 

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?

 

CALCULATE(SUM('sales'[amount]),ISBLANK('incorrect payments'[Id]))

 

Thank you

1 ACCEPTED SOLUTION
transform99
Resolver I
Resolver I

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 IDAmount

1

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

 

Incorrect Payment

Sales IDReason
4Declined Payment
8Invalid Payment Method
9Declined Payment
10Duplicate 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]
    )

 

 

 

 

transform99_0-1669940014464.png

 

View solution in original post

2 REPLIES 2
transform99
Resolver I
Resolver I

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 IDAmount

1

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

 

Incorrect Payment

Sales IDReason
4Declined Payment
8Invalid Payment Method
9Declined Payment
10Duplicate 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]
    )

 

 

 

 

transform99_0-1669940014464.png

 

That's exactly what I needed! Thank you so much!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.