Pivot table running total

[pangersandmash]

Hello

I am trying to replicate this pivot table with the values as running total in the monthly columns.

(see screenshot)

The incremental is added (or deducted each month). For example- my data has Lookup ID 50 (row 5) with a value of £6589  at December (the start of 2022). In January, they had a net movement of -£6589, hence the january value of 0.

Thanks

[Samantha_Jayne]

Hi @pangersandmash it may be helpful to provide the view of the starting point, we know where you want to end up, so that's a bonus.

Based on what I see here I would recommend the following tools, crosstab/transpose, formula tool and the running total tool to guide you on your journey.

[pangersandmash]

Thanks Samantha!

Those are the tools i have been playing around with. Can 'running total' only go down a column? ie. I would have to run total down, then transpose across?

I have got to here... but i need the columns to be 'start 2022',  'start 2022' + Jan, 'start 2022' + Jan+ Feb, 'start 2022' + Jan+ Feb+ Mar etc...

I have attached a data sample. The values i want to sum consecutively is 'ACV'.

[Samantha_Jayne]

Now seeing your data: I am thinking a transpose to get all your values in a long list (your data already looks transposed for the columns you need), then a multi-row formula (using the group by function by geography / account) to support your business logic , i.e. if x_column = "this" then add, else subtract. etc. 

[pangersandmash]

When you say 'your data already looks transposed', do you mean from my screenshot in the last comment?

I'm not sure how to implement that logic into the tool. Haven't used multi row before...

[Samantha_Jayne · Accepted solution]

Alex great catching up with you, as you can see in this workflow, the combinations of Cross Tab, Transpose and Multi-row formula is what you needed based on your input dataset. Enjoy solving.