Forum Discussion

Anne1234's avatar
Anne1234
Frequent Visitor
4 years ago
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 ...
Need Help
Tips and Tricks
pbix1's avatar
pbix1
Icon for Resolver II rankResolver II
4 years ago

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's avatar
Anne1234
Frequent Visitor
4 years ago

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