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

 

IndexDateMaterial 1RT M1Material 2RT M2...Material nRT Mn
105.11.2021221010 11
206.11.2021241020 01
307.11.202120241030 5556
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

Need Help
Tips and Tricks

5 Replies

  • 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

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    4 years ago

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