SUM Negative Account Balances

[dbragg34]

This may be a simiple solution but I am have trouble wrapping my head around it. I have a table that has all the transactions for each client (invoice amount, finanace charge and payments) when I sum the values in a table visual I get the clients total balance. I need to SUM the negative account balances but not all of the negative transactions in the table. For example 

Client	Transaction Type	Amount
A	Invoice	1000
A	Payment	-1000
A	Payment	-500

The client balance is -500 but when I use CALCULATE(SUM(Table[Amount]), Table[Amount]<0) function returns -1500. 

[LivioLanzo]

hi @dbragg34

Unless I am missing something, why not just do SUM(Table[Amount])  ?

[dbragg34]

That returns the total balance of the customer. Now say there are 3 other customers with the exact same transactions that function would return (4*-1500) = -6000. I want to sum the negative account balances (4*-500) = -2000. Hope that makes it clearer. 

[LivioLanzo]

As far as I can see, each customer has ( -1000) ( + 1000) ( - 500 ) which nets to  - 500 which then multiplied by 4 is 2000

[dbragg34]

So that was a bad example I gave you. Here is a better example. 

Transactions 

Client	Transaction Type	Amount
A	Invoice	1000
A	Payment	-1000
A	Payment	-500
B	Invoice	1000
B	Payment	-1000
B	Payment	-500
C	Invoice	1000
C	Payment	-1000
C	Payment	-500
D	Invoice	1000

Table Visual 

Client              Amount 

A                      -500

B                      -500

C                      -500

D                      1000

Total                  -500

Now I need to sum only the negative account balances (-1500) not the total negative transactions (-4500) 

Hope this helps 

[BobBI]

Hi ,

this example helps . thanks for providing sample date.

Weel , there are couple of way to achieve this. I achieved this by created a calculated table using your data.

I loaded your sample data into a table called 'dbragg Data' 🙂 then create anothe calcualted table and split Invoice and payment into different coulumns. ( you can also do this just by creating measure but this way you can see amount details- easy to verify).

Your

FinTable=

SUMMARIZE('dbragg Data'
          ,'dbragg Data'[Client]
          ,"Invoice",CALCULATE(sum('dbragg Data'[amount]),'dbragg Data'[transaction type]="Invoice")
          ,"Payment",CALCULATE(sum('dbragg Data'[amount]),'dbragg Data'[transaction type]="Payment")
                    
          )

create Measures

Invoice Amount =  Sum( Invoice)

Payment Amount = Sum(Payment)

Client Balance = CALCULATE(

                                        FinTable[Payment Amount]+FinTable[Invoice Amount],

                                         ISBLANK(FinTable[Payment])=FALSE()   ' this is the column created in summary table ( not the measure)

                                                 )

FinTablesource data and output

Hope this helps.  If you understand the login you can create this solution on your original table.

Cheers!

SS

[v-cherch-msft · Accepted solution]

Hi @dbragg34

You may refer to below measure:

NegativeSum =
VAR tableA =
    SUMMARIZE ( Table4, Table4[Client ], "a", CALCULATE ( SUM ( Table4[Amount] ) ) )
RETURN
    CALCULATE ( SUM ( Table4[Amount] ), FILTER ( tableA, [a] < 0 ) )

Regards,

Cherie

[ChrisMendoza]

@dbragg34, 

Will this work for you?

Measure = 
CALCULATE (
    SUMX (
        Table2,
        Table2[Amount]
    ),
    FILTER (
        Table2,
        Table2[Client] = Table2[Client]
    )
)