Forum Discussion
Solved
Running Total for 100 columns
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 ...
I have calculated the running total now after unpivoting with the help of here:
https://www.youtube.com/watch?v=EFQBMJ6JyCQ&t=313s
pbix1
Resolver II
Resolver IIHi 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