Sum of range with aggregated data

[LukeReds]

Hi to everyone,

i have a table like in the pic, I need to sum the values with this logic (table below, explosion of aggregated data abowe)

row 4: sum of (SellOut) row 4,3,2,1 = 15

row 5:  sum of (SellOut) row 5.4.3.2 = 23

row 6: sum of (SellOut) row 6,5,4,3 = 31

and so on

How can I sum in this way? If i had no aggregatiion I could use list.range but with aggregation?

Thank you in advance

[Anonymous · Accepted solution]

Hi @LukeReds 

You can put the following code to advanded editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlbSUTKxABIBRfkpBoZAhpFSrA5CyhJZygRZytQAWcoQRcoQWcoCRcoIRZcBTM4EbAaylBGyFIouUxQXwh1vhNvxIClLpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Week = _t, Prod = _t, SellOut = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Week", Int64.Type}, {"Prod", type text}, {"SellOut", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Prod"}, {{"Data", each Table.AddIndexColumn(_,"Index",1,1), type table}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Year", "Week", "SellOut", "Index"}, {"Year", "Week", "SellOut", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "Custom", each if [Index]>=4 then List.Sum(Table.SelectRows(#"Expanded Data",(x)=>x[Prod]=[Prod] and x[Index]<=[Index] and x[Index]>=[Index]-4+1)[SellOut]) else 0)
in
    #"Added Custom"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[LukeReds]

thank you v-xinruzhu-msft it's exactly what i needed 👍