cancel
Showing results for
Did you mean:
Highlighted
New Member

## Sum of Positive Numbers in Row (find debit and credit)

Hello, I am trying to add up all of the positive numbers in a row to find the debit amount and then all of the negative numbers to find the credit amount. What formula can I use in Power BI to figure this out?

In this example I am trying to calculate the Credit and Debit columns based off of the first 5 columns. Thank you!

Account Name   0 to 30 days       31 to 60 days   61 to 90 days    91 to 120 days    >120 days          Credit          Debit

Account 1                 \$150                     \$500                 \$100                   -\$450                -\$300                SUM OF -      SUM OF +

Account 2                -\$200                     \$300                 -\$250                  -\$100               \$400                  -\$550              \$500

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
New Member

## Re: Sum of Positive Numbers in Row (find debit and credit)

I ended up doing this:

credit = sumx(Filter('Table', 'Table'[61 to 90 (days)]<0), 'Table'[61 to 90 (days)])
+sumx(filter('Table', 'Table'[91 to 120 (days)]<0), 'Table'[91 to 120 (days)])
+sumx(filter('Table', 'Table'[121 to 150 (days)]<0), 'Table'[121 to 150 (days)])
+sumx(filter('Table', 'Table'[151 to 180 (days)]<0), 'Table'[151 to 180 (days)])
+sumx(filter('Table', 'Table'[181 to 240 (days)]<0), 'Table'[181 to 240 (days)])
+sumx(filter('Table', 'Table'[> 240 (days)]<0), 'Table'[> 240 (days)])

And it seems to be working! The filter was the key, it just needed a ton of them. Thank you for the advice!

4 REPLIES 4
Highlighted Helper III

## Re: Sum of Positive Numbers in Row (find debit and credit)

Hi,

Can you create

Debit Measure = sum ( if [0 to 30 days] < 0 ;[measure])+ sum ( if [31 to 60 days] < 0 ;[measure])+.....

Credit Measure = sum ( if [0 to 30 days] >= 0 ;[measure])+ sum ( if [31 to 60 days] >= 0 ;[measure])+.....

Does it help ??

Thanks

Highlighted Resolver I

## Re: Sum of Positive Numbers in Row (find debit and credit)

Hi,

Try using FILTER wrapped within a CALCULATE statement.  For example,

Credit  = CALCULATE ( SUM ( Sales[Value] ) , FILTER ( Sales , Sales[Value] >0))

Debit =  CALCULATE ( SUM ( Sales[Value] ) , FILTER ( Sales , Sales[Value] < 0))

Highlighted
New Member

## Re: Sum of Positive Numbers in Row (find debit and credit)

I ended up doing this:

credit = sumx(Filter('Table', 'Table'[61 to 90 (days)]<0), 'Table'[61 to 90 (days)])
+sumx(filter('Table', 'Table'[91 to 120 (days)]<0), 'Table'[91 to 120 (days)])
+sumx(filter('Table', 'Table'[121 to 150 (days)]<0), 'Table'[121 to 150 (days)])
+sumx(filter('Table', 'Table'[151 to 180 (days)]<0), 'Table'[151 to 180 (days)])
+sumx(filter('Table', 'Table'[181 to 240 (days)]<0), 'Table'[181 to 240 (days)])
+sumx(filter('Table', 'Table'[> 240 (days)]<0), 'Table'[> 240 (days)])

And it seems to be working! The filter was the key, it just needed a ton of them. Thank you for the advice!

Highlighted
Frequent Visitor

## Re: Sum of Positive Numbers in Row (find debit and credit)

Hello, try this

credit = if(sum(Sheet1[0 to 30])<0,sum(Sheet1[0 to 30]))+if(sum(Sheet1[31 to 60])<0,sum(Sheet1[31 to 60]))+if(sum(Sheet1[61 to 90])<0,sum(Sheet1[61 to 90]))+if(sum(Sheet1[91 to 120])<0,sum(Sheet1[91 to 120]))+if(sum(Sheet1[120 plus])<0,sum(Sheet1[120 plus]))

debit = if(sum(Sheet1[0 to 30])>0,sum(Sheet1[0 to 30]))+if(sum(Sheet1[31 to 60])>0,sum(Sheet1[31 to 60]))+if(sum(Sheet1[61 to 90])>0,sum(Sheet1[61 to 90]))+if(sum(Sheet1[91 to 120])>0,sum(Sheet1[91 to 120]))+if(sum(Sheet1[120 plus])>0,sum(Sheet1[120 plus]))

balance = sum(Sheet1[0 to 30])+SUM(Sheet1[31 to 60])+sum(Sheet1[61 to 90])+sum(Sheet1[91 to 120])+sum(Sheet1[120 plus]) Announcements #### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge! #### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event. Top Solution Authors
Top Kudoed Authors
Users online (994)