Forum Discussion

martinfernandez's avatar
martinfernandez
Frequent Visitor
7 years ago

Issue calculating Running total by columns

Hello,

 

I have a table with two main columns and a measure:

 

Rows: Vendor name
Columns: Transaction Date

Values: Accounting Balance

 

I need to create a matrix in Power BI with this data and the accounting balance added to the previous value.

 

 

As a case scenario I will use this example where the totals are calculated vertically (as usual):

 

This is what I really need:

 

A brief explanation of the previous table:

  1. Vendor 1 owed $200 on January 2017
  2. Vendor 1 owed $0 on February 2017 because he made a $200 payment
  3. Vendor 1 owed $50 on March 2017 because $0 + $50
  4. Vendor 1 owed $50 on April 2017 because he didn't make any payment.
  5. Vendor 1 owes $50 in total.
  6. etc

In other words I need to calculate the payments made by vendor for each month / column totals (that he owes in total). The last month (the current) will have the same debt as the total because it is cumulative.

 

Any suggestions? Thanks in advance

Need Help

2 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi martinfernandez,

     

    What does the original data look like? Please refer to the demo in the attachment. 

    1. If you have a date table, you can use this one.

    Measure 3 =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] <= MAX ( 'Table1'[Date] ) )
)

    2. If you don't have a date table, you can try this one.

    Measure =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER (
        ALL ( Table1 ),
        'Table1'[Date] <= MAX ( 'Table1'[Date] )
            && 'Table1'[Vendor] = MIN ( 'Table1'[Vendor] )
    )
)

    Issue-calculating-Running-total-by-columns

    Best Regards,
    Dale