Sum If Header Table Value <> Sum of Details Table Values

[tsql_simon]

SO I have two tables Bills and Payments, Each customer has One bill and they pay it off in stages over the Year, I am trying to get to the amount Unpaid BUT not count any bills which may have been over paid.

CustomerBills

Customer ID	Bill Amount
1	100
2	150
3	120
4	110
5	200

CustomersPayment

Customer ID	Paid Amount
1	50
1	50
2	50
2	50
3	120
4	50
4	50
4	10
5	50
5	50
5	50
5	75

I need to get to 50 Overdue and not the total sum which shows as 25 (Customer 5 is over paid)

Thanks in advance

Simon

[TomMartens · Accepted solution]

Hey @tsql_simon ,

not sure if I understand your requirement 100%.

I created the following 2 measures, this is more a habit than necessary for the solution:

Total Bill Amount = SUM('CustomersBill'[Bill Amount]) 

and

Total Paid Amount = SUM('CustomersPayment'[Paid Amount])

After this I created the following measure that now provides the solution (at least from my understanding of your requirement):

Overdue Amount = 
SUMX(
    VALUES(CustomersBill[Customer ID])
    , var billed = [Total Bill Amount]
    var paid = [Total Paid Amount]
    var diff = billed - paid
    return
    IF(diff > 0 , diff , BLANK())
)

This allows to a create a report like this:

Hopefully this provides what you are looking for.

Regards,

Tom

[tsql_simon]

Hi @TomMartens ;

You understood perfectly, Thank you very much, I have been fighting with similar SUMX logic but could not piece it together quite right. That works perfectly for me.

Kindest Regards

Simon