Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
dbragg34
Regular 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

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.

View solution in original post

7 REPLIES 7
LivioLanzo
Solution Sage
Solution Sage

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!  

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. 

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!  

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 

 

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)

                                                 )

 

 

 

FinTableFinTablesource data and outputsource data and output

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

Cheers!

SS

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.

@dbragg34

Will this work for you?

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

1.PNG

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.