Running Total for 100 columns

[Anne1234]

Hello community!

I am trying to calculate a running total in the Power Query Editor for 100 columns and I am looking for a simpler solution than manually inserting a new column 100 times. I have googled around for days now but haven't found a proper solution.

I am new to this community, but I will try my best to explain my problem short and clear:

My table consists of a Date-Column with 14 days (so 14 rows), and 100 Material-Columns, which list the demand of the material per date. What I need is another 100 columns, which list the cumulative demand until that date (see below):

Index	Date	Material 1	RT M1	Material 2	RT M2	...	Material n	RT Mn
1	05.11.2021	2	2	10	10		1	1
2	06.11.2021	2	4	10	20		0	1
3	07.11.2021	20	24	10	30		55	56
4	...	...	...	...		...	...	...

In Power Query Editor, I have used the following Code to calculate the running total for one material's demand:

= Table.AddColumn(#"Previous Step", "RT M1", each List.Sum(List.FirstN(#"Previous Step"[Material 1],[Index])))

Is there a possibility to automate the step to add a new column for each material, that show the running total for each material?

Thank you already for your help!

Anne

[jdbuchanan71]

@Anne1234 

For the running total I think you want 2 measures.

The first one is just the sum of the Demand column.

Demand Amount = SUM ( 'YourTable'[Demand] )

Then it is just a matter of adding the date field from your Dates table and having a measure like this. 

Running Total = 
VAR _End = MAX ( DATES[Date] )
RETURN
CALCULATE (
    [Demand Amount],
    FILTER ( ALLSELECTED ( DATES[Date] ),
    DATES[Date] <= _End )
)

[pbix1]

Hi Anne1234

I think the best thing to do is to unpivot this, and then you can do a running total on one column. You can do this easily in Power Query. I'm not sure how to unpivot in DAX though, or even if you can.

The following DAX can be adapted to do a running total. I hope this helps, but it might need someone else to help tune this so it fits your situation.

Running Total = var CurrentDate = SalesByOrderDateKey[Sales_OrderDateKey]

var CurrentGroup = SalesByOrderDateKey[Group]

var FilteredTable = filter(SalesByOrderDateKey,SalesByOrderDateKey[Sales_OrderDateKey]<=CurrentDate

&& SalesByOrderDateKey[Group] = CurrentGroup)

return

calculate(sum(SalesByOrderDateKey[Order Quantity]),FilteredTable)

Neil

[Anne1234]

Hello pbix1,

unpivoting is a great idea! However, is there any way how I can calculate the running total in power query, and not as DAX? Because I would need to pivot the table afterwards again.

Thank you for your help,

Anne

[Anne1234 · Accepted solution]

I have calculated the running total now after unpivoting with the help of here:
https://www.youtube.com/watch?v=EFQBMJ6JyCQ&t=313s

[jdbuchanan71]

@Anne1234 

You should unpivot all of your material columns so you end up with 3 total columns.
Date / Material / Demand

https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f5882...

Then you would join that to a date table in your model and add a running total measure.