How to count each time some rows sum x number?

[jmontes1810]

Hi community,
This time, I need some help from you to count or assign a count each time some rows sum X quantity.
For example, in the next table I would like to know how to get the column "Pallet List 2"
My required conditionals are:

• Count or asign a number echa time the rows sum 80 or less, but not more than 80.
• Only applyes for rows with qtys less than 80, reason why there's no count on the last two rows.

 
I would really appreciate if you can help me Community, 
Thnks in advantage.

[smpa01]

@jmontes1810  can you try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndJLDsIwDATQq6Csi2R7/InPUvX+14BSqKipQCFLa54msTLPDenamRXRpnZ5O0xlsE3bMj2QwQKcY0h7EHMfRev1/moaRC7umlYy8qspBR+ZcyQvJARY+hjamrhk8B3B7vszLRk/RTg2ScnEKdIdBSi5vqmv6Lpnkjzs+NWYnpHlBg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DN = _t, QTY = _t, #"Pallet List 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DN", Int64.Type}, {"QTY", Int64.Type}, {"Pallet List 2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Pallet List 2"}),
    Value = #"Removed Columns"[QTY],
    Loop = List.Generate(
                          ()=>[i=0,k=if Value{i}>=80 then 0 else Value{i}],
                          each [i]<List.Count(Value),
                          each [i=[i]+1, k=if Value{i}>=80 then 0 else Value{i}+[k]],
                          each [k]
    ),
    Custom1 = Table.FromColumns(Table.ToColumns(#"Changed Type")&{Loop},List.Combine({Table.ColumnNames(#"Changed Type"),{"Helper"}})),
    #"Added Custom" = Table.AddColumn(Custom1, "Custom", each if [QTY]<80 then Number.IntegerDivide([Helper],80)+1 else null)
in
    #"Added Custom"

[jmontes1810]

Im about to try it... 
Just to confirm, in my new table, in the advanced editor I just need to paste the code youre providen me but replacing each "Table.XXX" for the name of my table uploaded table. Right?

[smpa01]

@jmontes1810  if you want to try out PQ, keep things unchanged from 

#"Removed Columns"

[jmontes1810]

I think the PQ form works, but, when I use the code, the data just retrive me the data of this example table. Now, what about if i want to use another Sheet/data? What should I do or change? My data wil be updated daily 

[smpa01]

@jmontes1810 load your data in PQ and that would result in Source followed by #"Changed Type". Once you have that and as long as the structure of the table is similar to the sample, you don't need to change anything till the end except pasting it in the query editor. If you prefer you can adapt the dax code as well.

[jmontes1810]

Hi @smpa01 
It almost works, but look... Are some count where the column sum over than 80, like the 5 and 6, in these two are 90 pcs.

I attach the data bellow...

DN	QTY	Helper	Custom
3961178426	10	10	1
3961210671	10	20	1
3961254067	10	30	1
3961275157	10	40	1
3961377408	10	50	1
3961578272	10	60	1
3962085691	10	70	1
3962464346	10	80	2
3964382459	10	90	2
3964382465	10	100	2
3964681873	10	110	2
3964733826	10	120	2
3964787919	10	130	2
3964907400	10	140	2
3964914982	10	150	2
3966179015	10	160	3
3966243202	10	170	3
3961079096	20	190	3
3961079104	20	210	3
3961322655	20	230	3
3964714777	20	250	4
3964892006	20	270	4
3966154221	20	290	4
3964580786	30	320	5
3966243208	30	350	5
3966246800	30	380	5
3964580778	40	420	6
3964056235	50	470	6
3964712764	50	520	7
3964712772	50	570	8
3964825696	80	0
3965997185	680	0

[smpa01]

@jmontes1810  can you please show what is your desired output for the dataset you provided?

[jmontes1810]

@smpa01 
This is what we actually get...

This is the desired output

DN	QTY	Helper	Desired Count
3961178426	10	10	1
3961210671	10	20	1
3961254067	10	30	1
3961275157	10	40	1
3961377408	10	50	1
3961578272	10	60	1
3962085691	10	70	1
3962464346	10	80	2
3964382459	10	90	2
3964382465	10	100	2
3964681873	10	110	2
3964733826	10	120	2
3964787919	10	130	2
3964907400	10	140	2
3964914982	10	150	2
3966179015	10	160	3
3966243202	10	170	3
3961079096	20	190	3
3961079104	20	210	3
3961322655	20	230	3
3964714777	20	250	4
3964892006	20	270	4
3966154221	20	290	4
3964580786	30	320	5
3966243208	30	350	5
3966246800	30	380	6
3964580778	40	420	6
3964056235	50	470	7
3964712764	50	520	8
3964712772	50	570	9
3964825696	80	0
3965997185	680	0

[ronrsnfld · Accepted solution]

You can create a "conditional" running total using List.Generate; then use List.Accumulate to develop the Pallet List.

Data

let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DN", type text}, {"QTY", Int64.Type}}),

//generate list of pallets
//replace >=80 with nulls to satisfy that condition
    qty= List.Buffer(List.Transform(#"Changed Type"[QTY], each if _ >=80 then null else _)),

//Generate a running total of the qtys that start over when there is a null 
// (ie an entry that was >=80) or when the running total reaches a Max of 80
    qtyList = List.Generate(
        ()=>[qt= qty{0}, idx=0],
        each [idx] < List.Count(qty),
        each [qt= if qty{[idx]+1} = null then null 
                    else if [qt] = null then  qty{[idx]+1} 
                    else if [qt] + qty{[idx]+1} <=80 then [qt] + qty{[idx]+1}
                    else qty{[idx]+1},
                idx = [idx]+1],
        each [qt]),

//generate the Pallet list by incrementing the list whenever the qty entry = the entry in the qtyList
//Note that the seed has to be {0} as {} or {null} + any number => null
//So we make the seed a {0} and remove that first entry when done generating the list.
    palletList = List.RemoveFirstN(
    List.Accumulate({0..List.Count(qty)-1},{0},(state,current)=>
        if qtyList{current} = null then state & {null} 
            else if qtyList{current} = qty{current} then state & {List.Max(state)+1} 
            else state & {List.Max(state)}
    ),1),

//add to table
    tbl = Table.FromColumns( 
    Table.ToColumns(#"Changed Type") & {palletList},
    Table.ColumnNames(#"Changed Type") & {"Pallet List"})

in  
    tbl

Results

[smpa01]

@jmontes1810  will respond back to you

[smpa01]

@jmontes1810  sorry I can't seem to resolve this 

@ronrsnfld  are you able to take a look please ?

[ronrsnfld]

I posted a possible solution using List.Generate and List.Accumulate.  Thanks for the ping.

[smpa01]

@jmontes1810  in DAX

Column =
VAR _0 =
    CALCULATE ( MAX ( tbl[Index] ) )
VAR _1 =
    IF (
        tbl[QTY] >= 80,
        0,
        CALCULATE ( SUM ( tbl[QTY] ), FILTER ( ALL ( tbl ), tbl[Index] <= _0 ) )
    )
VAR _2 =
    IF ( tbl[QTY] < 80, QUOTIENT ( _1, 80 ) + 1, BLANK () )
RETURN
    _2

You need to have an index column in the data in order for DAX to work

[Greg_Deckler]

@jmontes1810 Can you post sample data as text? You will need an Index column to define "before". Then something Cthulhu: Cthulhu - Microsoft Power BI Community

[jmontes1810]

Thnks for your promp reply @Greg_Deckler 
Here are the sample data in text form, hoping it can help.
As my mortal view, I think Cthulhu cant works for what im seeking 'cause it count the times some same value/text appears in consecutive form due to an index order. 
My index order in this case is to sort from smaller to biger quantity. But, what about the sum conditional? To count each time the rows sum 80 and only for qtys less than 80.  I hope you can elp me.

DN	QTY	Pallet List 2
3964811437	10	1
3965357319	10	1
3964870118	10	1
3964811439	10	1
3964870119	10	1
3966266495	20	1
3964892320	20	2
3962033596	20	2
3964892321	30	2
3963548154	60	3
3964892322	70	4
3964730916	80	-
3964906757	100	-

[Greg_Deckler]

@jmontes1810 Not sure QTY sort is going to work but will have to try some things.

[jmontes1810]

@Greg_Deckler Thnk you Greg, I hope you can find something that could work. 

[Greg_Deckler]

@jmontes1810 If you add an index, try:

Column = 
    VAR __Limit = 80
    VAR __Value = SUMX(FILTER(ALL('Table8'),[Index]<=EARLIER('Table8'[Index])),[QTY])
RETURN
    TRUNC(DIVIDE(__Value,__Limit))+1

[jmontes1810]

Hi @Greg

It almost works, but look... Are some count where the column sum over than 80, like the 5 and 6, in these two are 90 pcs.