SUM

You could use DAX to create a calculated column like

Total Quantity =
IF (
    'Table'[Category] = "Article",
    CALCULATE (
        SUM ( 'Table'[Needed] ),
        ALLEXCEPT ( 'Table', 'Table'[Column no.] )
    ),
    'Table'[Needed]
)

Hi AllanBerces ,

 

Try the following approach:

• Do a group by Category and Column N.º
• Add a Sum of needed and a All Rows

• Expand the columns Unit and Need
• On the Total Qty do a Replace values
  • Change just one value by whateve you want
• Redo the code of the Replace step to:

= Table.ReplaceValue(#"Expanded Allvalues",each [Total Qty], each if [Category] = "Object" then [Needed] else [Total Qty] ,Replacer.ReplaceValue,{"Total Qty"})

 

Final result:

 

 

Check full code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgYDAyAwVNJRcnUEEkamQMKxqCQzOSdVKVYHqxpDEzQ1/klZqcklcCMMgARUCFPWkFqSCIcZIavA7nbsSmIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column No." = _t, Unit = _t, Needed = _t, Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column No.", type text}, {"Unit", type text}, {"Needed", Int64.Type}, {"Category", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Category", "Column No."}, {{"Total Qty", each List.Sum([Needed]), type nullable number}, {"Allvalues", each _, type table [#"Column No."=nullable text, Unit=nullable text, Needed=nullable number, Category=nullable text]}}),
    #"Expanded Allvalues" = Table.ExpandTableColumn(#"Grouped Rows", "Allvalues", {"Unit", "Needed"}, {"Unit", "Needed"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Allvalues",each [Total Qty], each if [Category] = "Object" then [Needed] else [Total Qty] ,Replacer.ReplaceValue,{"Total Qty"})
in
    #"Replaced Value"