Forum Discussion

CameronKudos's avatar
CameronKudos
Helper I
4 years ago
Solved

Stuck on Creating reverse running total based on other columns

Hi all, I desperately need to figure this out for my work.   Here's the sample data: https://drive.google.com/file/d/1dI7COyElnNXb33iSk1eiau8ESONHLphf/view?usp=sharing   Here's the overview:   ...
Need Help
  • Thingsclump's avatar
    Thingsclump
    4 years ago

    Hi CameronKudos 

     

    I believe you need to do a grouped running total. The only difference is that you need to subtract rather than add. My suggestion is multiply daily rate column with -1 and do a grouped total. It should work. You can search for running total grouped on Google and you will get lot of articles. I just found that this one is useful.

     

    https://www.goodly.co.in/running-total-power-query/ 

     

    Let me know how this works.

     

    Thanks

Thingsclump's avatar
Thingsclump
Resolver V
4 years ago

Hi CameronKudos 

 

I believe you need to do a grouped running total. The only difference is that you need to subtract rather than add. My suggestion is multiply daily rate column with -1 and do a grouped total. It should work. You can search for running total grouped on Google and you will get lot of articles. I just found that this one is useful.

 

https://www.goodly.co.in/running-total-power-query/ 

 

Let me know how this works.

 

Thanks

  • CameronKudos's avatar
    CameronKudos
    Helper I
    4 years ago

    Thanks for that. this is the code that I went with, but it didn't work. Where do I put the minus symbol? Should I not be using the Index column?
    = Table.AddColumn(
    BufferedTable,
    "Running Total",
    (OutTable) =>
    List.Sum(
    Table.SelectRows(
    BufferedTable,
    (InTable) => InTable[Index] <= OutTable[Index]
    and
    InTable[Daily_Rate_Based_On_Days_Remaining__c] = OutTable[Daily_Rate_Based_On_Days_Remaining__c])[Total_Remaining_Cost__c]))

  • CameronKudos's avatar
    CameronKudos
    Helper I
    4 years ago

    All good, I got it by adding a running total to the daily rate, then using that colum to subtract from the total amount remaining. Thank you