- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- SUM Negative Account Balances

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

dbragg34

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-16-2018
06:49 AM

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.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

v-cherch-msft

Community Support Team

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-16-2018
11:37 PM

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

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.

If this post

7 REPLIES 7

LivioLanzo

Super User

Re: SUM Negative Account Balances

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-16-2018
07:00 AM

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

Re: SUM Negative Account Balances

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-16-2018
07:13 AM

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

Super User

Re: SUM Negative Account Balances

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-16-2018
07:18 AM

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

Re: SUM Negative Account Balances

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-16-2018
07:46 AM

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

ChrisMendoza

Established Member

Re: SUM Negative Account Balances

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-16-2018
08:32 AM

Will this work for you?

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

BobBI

Member

Re: SUM Negative Account Balances

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-16-2018
08:43 AM

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)

)

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

Cheers!

SS

v-cherch-msft

Community Support Team

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-16-2018
11:37 PM

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

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.

If this post