cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.