SUM

[AllanBerces]

Hi can someone help me on my PQ. I have table and i need a sum and if condition

- IF the category is equal to Object then the value is equal to Needed Column

- IF the category is equal to Article then sum all the value in Needed column with same Column No. value

OUTPUT

39 is the sum of needed column 25 and 14

Column No.	Unit	Needed	Category	TOTAL QTY
CCCCCC00001	EA	25	Article	39
CCCCCC00001	EA	14	Article	39
Object	EA	20	Object	20
Object	EA	1	Object	1
Object	EA	1	Object	1
Object	EA	1	Object	1

[johnt75 · Accepted solution]

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]
)

[MFelix · Accepted solution]

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"

[johnt75 · Accepted solution]

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]
)

[AllanBerces]

Hi @johnt75 @MFelix thank you very much for the reply work as i need