cancel
Showing results for
Did you mean:
Frequent Visitor

## SUM Negative Account Balances

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: SUM Negative Account Balances

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Super User

## Re: SUM Negative Account Balances

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

Did I answer your question correctly? Mark my answer as a solution!

Proud to be a Datanaut!

Frequent Visitor

## Re: SUM Negative Account Balances

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.

Super User

## Re: SUM Negative Account Balances

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

Did I answer your question correctly? Mark my answer as a solution!

Proud to be a Datanaut!

Frequent Visitor

## Re: SUM Negative Account Balances

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

Established Member

## Re: SUM Negative Account Balances

Will this work for you?

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

Member

## Re: SUM Negative Account Balances

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

Community Support Team

## Re: SUM Negative Account Balances

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.