Forum Discussion

zoberender's avatar
zoberender
Frequent Visitor
3 years ago

Running Total with Multiple Columns

Hi PBI Community, 

 

I have a dataset where I have a Category, Date, Transaction # and Amount.  I also have a date table.  I am trying to create matrix that rolls forward the ending balance to the next day.  

 

A sample dataset is:

DateTransaction #CategoryAmount
10/03/2022OBOpening Balance9
10/03/20222uyg4Debit12
10/03/20226scq9Debit7
10/03/2022z2dt1Credit-9
10/03/2022eu3x0Credit-12
10/04/2022s1c4dDebit7
10/04/2022h89vgCredit-12
10/04/2022au7fiDebit7
10/04/2022xrltpDebit9
10/05/2022f0soxCredit-12
10/05/2022sj2yaDebit12
10/05/2022oupnyDebit9
10/05/2022c9tohCredit-7
10/05/2022jl8zmCredit-9
10/05/20225eta6Debit12
10/05/202204f29Credit-7

 

Desired Output:

DatePrior Day EndOpening BalanceDebitCreditEnd Balance
10/03/20220919-217
10/04/20227 23-1218
10/05/202218 33-3516

 

When I create the matrix, I use Date for the rows and Category for the columns.  I then have a couple measures:

 

Total Amount = sum('Final Data'[Amount])

Running Total = 
CALCULATE (
    [Total Amount], 
    FILTER(
        ALL ( 'Date' ), 
        'Date'[Date] <= MAX ('Date'[Date] )
    )
)

 

 

I can put the Total Amount in the Matrix and I get columns with the different categories like expected.  But I can't figure out how to get the Running Total into the matrix as one column.  If I add it in the values section I get a column for each Category.  I really only want the total for the day, not for each column.  

 

I have searched all over and tried a bunch of different things, but I just can't figure out how to do this!  Any help would be appreciated.  

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    zoberender , Thsi should very similar to how we build inventory

     

    examples

    Inventory / OnHand
    [Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

    Inventory / OnHand
    CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

     

     

    Power BI Inventory On Hand
    Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

    • zoberender's avatar
      zoberender
      Frequent Visitor
      3 years ago

      Thanks for the response Amit.  I watched the video and your example is slightly different than mine but a similar concept. The main difference is that you have separate tables for sales, purchases and inventory which allows you to put them in individual columns of the matrix.  My category is one column of a table that I'm using in the matrix.  

       

      I simplified the data to provide an example.  In reality, the category column has many different values (interest received, maturities, returns, debits, credits etc).  While I could write individual measures to calculate the amount of each category, this is not optimal as there could be "new" categories come in as the data is refreshed.