cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dbragg34 Frequent Visitor
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 
AInvoice 1000
APayment-1000
APayment-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
Community Support Team

Re: SUM Negative Account Balances

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

1.png

 

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
Super User

Re: SUM Negative Account Balances

hi @dbragg34

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!  

dbragg34 Frequent Visitor
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
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!  

dbragg34 Frequent Visitor
Frequent Visitor

Re: SUM Negative Account Balances

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

 

Transactions 

Client Transaction TypeAmount
AInvoice1000
APayment-1000
APayment-500
BInvoice1000
BPayment-1000
BPayment-500
CInvoice1000
CPayment-1000
CPayment-500
DInvoice1000

 

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 

ChrisMendoza Established Member
Established Member

Re: SUM Negative Account Balances

@dbragg34

Will this work for you?

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

1.PNG

 

BobBI Member
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)

                                                 )

 

 

 

fin table.JPGFinTableoverview.JPGsource 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
Community Support Team

Re: SUM Negative Account Balances

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

1.png

 

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.