locked
Trying to Produce a Cumulative or Running Total for Quantity RRS feed

  • Question

  • The following statement produces a total Quantity value for each row instead of a cume value.  It seems that the row filter isn't working as I had expected.  Btw, the added index starts at 0 and increments by 1.  Can this be modified to produce the results I need? ...

    #"Added Custom" = Table.AddColumn( #"Added Index", "Cume.Quantity", each List.Sum( Table.SelectRows( #"Added Index", each List.Contains( List.Generate( ( ) => [Index], each _ > -1, each _ -1 ), [Index] ))[Quantity] ))

    Wednesday, August 5, 2015 7:35 PM

Answers

All replies

  • Use the technique shown here:

    http://www.daxpatterns.com/cumulative-total/

    Wednesday, August 5, 2015 8:18 PM
  • Power Query doesn't use DAX
    Wednesday, August 5, 2015 8:28 PM
  • Load it into your data model and create a measure, Power Query will load the data into your Power Pivot data model.
    Wednesday, August 5, 2015 8:39 PM
  • I only want to produce an Excel table.  Can you help with Power Query Language constructs?
    Wednesday, August 5, 2015 8:40 PM
  • Or use the technique found here:

    http://www.excelguru.ca/blog/2015/03/31/create-running-totals-in-power-query/

    Wednesday, August 5, 2015 8:40 PM
  • Looking for a solution that doesn't use a Custom Function call.  Do you have a workable modification to the statement provided above?
    Wednesday, August 5, 2015 8:44 PM
  • Wait, what's wrong with a custom function call, it is all M code?
    Wednesday, August 5, 2015 8:48 PM
  • Here is a non-functional Option:

    Cum = Table.AddColumn(AddIndex, "Cum", each List.Sum(List.FirstN(AppendLastRow[Amount],[Index]+1))),

    taken from this example: http://www.thebiccountant.com/2015/08/04/waterfall-chart-using-power-query/


    Imke

    • Marked as answer by Mark Weisman Wednesday, August 5, 2015 9:09 PM
    Wednesday, August 5, 2015 8:51 PM
  • Very interesting ... please allow me to evaluate, and I'll report back.
    Wednesday, August 5, 2015 9:00 PM
  • Bingo! So, sweet.  Here's my use case ... 

    Table.AddColumn( #"Added Index", "Cume.Quantity", each List.Sum( List.FirstN( #"Added Index"[Quantity], [Index] + 1 )))

    Wednesday, August 5, 2015 9:09 PM