Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Cumulative Sum using List.Accumulate with a twist

I have a function that creates a running total. As it currently stands (without the red code) it will create a running sum for each group. I would like to expand on this somehow to include logic that says restart the running sum at the end of the current group or when the current number is greater than the number in the column Max Container. My attempt to do is in in red. What am I missing?

 

(MyTable as table) as table =>
let
Source = Table.Buffer(MyTable),
TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
current = List.Skip(List.Accumulate(Source[SKU Cumulative Qty],{0},(current,state) => (current & {List.Last(current) + state}) or (current & {List.Last(Source[Max Container]) + state}) )),
AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{current},TableType)
in
AddedRunningSum

2 ACCEPTED SOLUTIONS

hey

and what was the BIN# in your post?

Now this does simplify things 🙂

This will be my last post on this thread 😉

3 beers - some kudoes and 3 solutions as min, allright @Anonymous ? 😉

(tTable as table) as table =>
let
    Group = Table.Group(tTable, {"Order"}, {{"AllRows", each _}}),
    fnRowIndex = (tbl as table, sumcolumn as text, rowindex as number) =>
        let
            #"Removed Other Columns" = Table.SelectColumns(tbl,{sumcolumn, "Index"}),
            #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [Index] <= rowindex),
            #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{sumcolumn, "Temp"}}),
            #"Grouped Rows" = Table.Group(#"Renamed Columns", {}, {{"RunningTotal", each List.Sum([Temp]), type number}}),
            RunningTotal = Record.Field(#"Grouped Rows"{0},"RunningTotal")
        in
            RunningTotal,
      
    AddIndex = Table.TransformColumns
        (
            Group,
            {{"AllRows", (transform) => Table.AddIndexColumn(transform, "Index", 1)}}
        ),
    AddRunSum = Table.TransformColumns
        (
            AddIndex,
            {{"AllRows", (transfom) => Table.AddColumn
                (
                    transfom,
                    "Run",
                    (add)=> fnRowIndex(transfom, "SKU Count", add[Index]) 
                )}}
        ),
    AddBin = Table.TransformColumns
        (
            AddRunSum,
            {{"AllRows", (transform) => Table.AddColumn
                (
                    transform,
                    "Bin used",
                    (add)=> Number.RoundUp(add[Run]/add[Max Bin])
                )}}
        ),
    ExpandAllRows = Table.ExpandTableColumn(AddBin, "AllRows", {"SKU Count", "Max Bin", "Bin used"}, {"SKU Count", "Max Bin", "Bin used"})
    /*ExtractBin = Table.AddColumn
        (
            AddBin,
            "Bin used",
            each [AllRows][Bin used]
        ),
    #"Hinzugefügter Index" = Table.AddIndexColumn(ExtractBin, "Index", 1, 1),
    AddMax =     Table.AddColumn
        (
             #"Hinzugefügter Index",
            "Max List",
            each List.Max([Bin used])
        ),
    NumberMaxList = Table.TransformColumnTypes(AddMax,{{"Max List", Int64.Type}}),
    AddChangedUsedBin = Table.AddColumn
        (
             NumberMaxList,
    /        "New Bin used",
            (add) => List.Transform(add[Bin used], (listtransform) => if add[Index] = 1 then listtransform else listtransform + List.Sum
                (
                    Table.SelectRows
                        (
                            NumberMaxList, (select)=> select[Index]< add[Index]
                        )[Max List]
                )
                )

        ),
    AddNewColumn = Table.AddColumn
        (
            NumberMaxList,
            "Final table",
            (add)=> Table.Join(add[AllRows], "Index", Table.AddIndexColumn(Table.FromList(add[New Bin used],Splitter.SplitByNothing(),{"Bin Used"},ExtraValues.Error),"Index1",1),"Index1")
        ),
    DeletedRows = Table.RemoveColumns(AddNewColumn,{"AllRows", "Bin used", "Index", "Max List", "New Bin used"}),
    Expand = Table.ExpandTableColumn(DeletedRows, "Final table", {"SKU Count", "Max Bin", "Bin Used"}, {"SKU Count", "Max Bin", "Bin Used"})
    */
in
    ExpandAllRows

have a nice evening

Jimmy

 

View solution in original post

:D:D

I don't know if I should laugh o cry

 

I don't know how you are applying my function.. you have to pass your whole table into the fucntion and using the result not adding columns to multply it 😄

Bye Jimmy

View solution in original post

32 REPLIES 32
Anonymous
Not applicable

Nevermind! (Tell me if I'm wrong)

 

Simply adding  SKU to the last step includes it in the output! 

 

ExpandAllRows = Table.ExpandTableColumn(AddBin, "AllRows", {"SKU Qty", "Max Container", "Bin used","Final SKU"}, {"SKU Qty", "Max Container", "Bin used","Final SKU"})

 

Careful Jimmy the student is becoming the teacher! ha 

 

 

however @Anonymous 

always ready to get some nice and tricky request by you 😄

Jimmy

Anonymous
Not applicable

Thanks again Jimmy.

 

I am looking through the results now and noticed a few instances where the bin's are being overfilled? 

Hey

could you please share you real-life file.

What does overfill mean? that the function has calculated for example calculated 20 bins, when the order would need 30?

However, please share the file

 

Jimmy

Anonymous
Not applicable

 Sure. I highlighted 4 instances where the sum of the rows for each assigned bin exceeds the max bin limit, 

 

Not sure how to share excel files here so I uploaded to Google Drive:

Let me know if you cannot receive the file

https://drive.google.com/open?id=1W2LOCnsevvRTTDcuaRHQ2mcVo-Eyy6Rz 

 

 

hello @Anonymous 

 

this is a .csv file. Not possible to highlight there something. But nevertheless... don't understand .. we never talked about max bin limit... the number we calculated was always how many bins you need to ship the order, considereing the sku count and the max sku count per bin... so we this framework, how would it possible that something gets exceeded?

 

Bye

Jimmy

Anonymous
Not applicable

My apologies, excel file is uploaded. to google drive.

 

I think the excel file will show you some good examples. What I called Max Bin Limit is the same as max SKU Count per bin.

 

For example if I have 3 rows within 1 Order and SKU count for each of these 3 rows is 10 and I have a Max Bin of 10 for this Order Then I would use 3 Bins and each row would be a new Bin Used number. Said in other words, the value in Max Bin column is the max allowed number SKU Counts that can fit into a single Bin. Hope that clears it up? 

Hello @Anonymous 

 

are  you kidden me?

I mean, I'm loosing here time to check and understand (already used too much time) and at the end you are presenting wrong data. I mean when you ask a professional you pay deerly for all this. I did it for free in a very professional way to help....

 

I've downloaded your data, applied my funciton and it works perfectly fine as you have requested... a running sum to understand at wich point you need a new bin. The data you are represending in the file was not calculated with my function. see enclosed your version, and my version

your data

image.png

 

your raw data calculated with my function

image.png

 

the next thing is that you asked for a running sum, considering thi SKU count till that order line... but not considering that if a complete order line has to placed into a new bin once it's not possilbe to place it fully in the bin used before. This does mean you cant do a valuation like you did.

In Other words...

line 1 44000

line 2 5000

the function says 1 for line 1 and 2 for line 2. for a third line he would count 44000, 5000 and checks if for line 3 you would need a third bin this means that line 2 with bin 2 indicated are considere to be placed 500 in bin 1. So how can you sum all bin nr. 2 and check if this exceeds the max bin? Doesn't make any sense.

 

Sorry, but I'm off

 

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

Anonymous
Not applicable

@Jimmy801 Obviously you are frustrated. There has been assumptions on both of our ends that has created this. I know there is logic built in the function that determines when the Bin used increments up by 1. I thought it would be a simple fix to change that logic from whatever it is now to increment up once the runningsum exceeds the Max Bin value. I thought that was obvious but as this post has proven nothing can be assumed. If you wish to not continue that is your perogative and I will respect it. Thank you for your time and efforts. 

:D:D

I don't know if I should laugh o cry

 

I don't know how you are applying my function.. you have to pass your whole table into the fucntion and using the result not adding columns to multply it 😄

Bye Jimmy

Hello

 

this detail you never told us

should the Bin # consider also empty space from the order before, or always start from 0 meaning if from order there is almost one empfty bin left, the new order is consideres to be placed into a new bin or can it use the empty space from order 1?

 

First, do your accumulate as part of a group by operation on the Order Column, as this way you won't need to handle that column in your accumulate.

 

Here is the simple, how many bins approach (not tested to see if this fully works):

List.Accumulate(Table.ToRecords(_), [Running Sum = 0, Bin Count = 1], (current, next) =>
   let
      #"Running Sum" = current[Running Sum] + next[SKU Count]
   in
      if #"Running Sum" > next[Max Bin] then
         [Running Sum = next[SKU Count], Bin Count = 1 + current[Bin Count]]
      else
         [Running Sum = #"Running Sum"] & current[[Bin Count]]
)[Bin Count] //Remove this if you want to see how many are left in the last bin

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors