Forum Discussion

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
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

Anonymous

Not applicable

4 years ago

Is it correct that the [Days Remaining] column also resets at the Account ID change? If so, you could Group on the Account ID, use the Max aggregation on Days Remaining (let's name that column MaxDay) and an All Rows Aggregation, and then expand the All Rows column. Then, you could do row arithmetic like:

= Table.AddColumn(PriorStepOrTableName, "Amount Left", each [Total Remaining Cost] - (([MaxDays -Days Remaining) * Rate))

--Nate