Create a filter that is the sum of other 3

[Fabio_B]

Hi all, 

I have a database in which the data are like this, to make an easy example:

Pn	Plant	Requirement Date	Requirement Type	Requirement Quantity
1	A	1/01/2024	Firm	10
1	A	1/01/2024	Raw Material	10
1	A	1/01/2024	Planning	10
1	A	2/01/2024	Firm	10
1	A	2/01/2024	Raw Material	10
1	A	2/01/2024	Planning	10
1	A	3/01/2024	Firm	10
1	A	3/01/2024	Raw Material	10
1	A	3/01/2024	Planning	10

I would like to create a new line called "Requirement DOD" with the sum of Plannin, Raw Material and Firm values, to achieve something like this:

Pn	Plant	Requirement Date	Requirement Type	Requirement Quantity
1	A	1/01/2024	Firm	10
1	A	1/01/2024	Raw Material	10
1	A	1/01/2024	Planning	10
1	A	1/1/2024	Requirement DOD	30
1	A	2/01/2024	Firm	10
1	A	2/01/2024	Raw Material	10
1	A	2/01/2024	Planning	10
1	A	2/1/2024	Requirement DOD	30
1	A	3/01/2024	Firm	10
1	A	3/01/2024	Raw Material	10
1	A	3/01/2024	Planning	10
1	A	3/1/2024	Requirement DOD	30

Do someone have an idea on how to achieve this?
Many thanks in advance for all your kind comments.

Best Regards

Fabio

[sevenhills · Accepted solution]

You can try like this in Power Query:

Table with Summary Rows

let 
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYkN9A0N9IwMjEyDbLbMoFyRkoBSrg11FUGK5gm9iSWpRZmIOfpUBOYl5eZl56ZiqjAjaaES0jUZE2WhM0EZjom00xm1jLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Pn = _t, Plant = _t, #"Requirement Date" = _t, #"Requirement Type" = _t, #"Requirement Quantity" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pn", Int64.Type}, {"Plant", type text}, {"Requirement Date", type date}, {"Requirement Type", type text}, {"Requirement Quantity", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Pn", "Plant", "Requirement Date"}, {{"Requirement Quantity", each List.Sum([Requirement Quantity]), type nullable number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Requirement Type", each "Requirement DOD", type text),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Pn", "Plant", "Requirement Date", "Requirement Type", "Requirement Quantity"}),
    #"Appended Query" = Table.Combine({#"Reordered Columns", #"Changed Type"}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Pn", Order.Ascending}, {"Plant", Order.Ascending}, {"Requirement Date", Order.Ascending}, {"Requirement Type", Order.Ascending}})
in
    #"Sorted Rows"

Your data at step #"Changed Type":

Final Ouput:

Note: If you want as separate tables, like one as source and other as with summary rows, you can do that too.

Table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYkN9A0N9IwMjEyDbLbMoFyRkoBSrg11FUGK5gm9iSWpRZmIOfpUBOYl5eZl56ZiqjAjaaES0jUZE2WhM0EZjom00xm1jLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Pn = _t, Plant = _t, #"Requirement Date" = _t, #"Requirement Type" = _t, #"Requirement Quantity" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pn", Int64.Type}, {"Plant", type text}, {"Requirement Date", type date}, {"Requirement Type", type text}, {"Requirement Quantity", Int64.Type}})
in
    #"Changed Type"

let
    Source = Table,
    #"Grouped Rows" = Table.Group(Source, {"Pn", "Plant", "Requirement Date"}, {{"Requirement Quantity", each List.Sum([Requirement Quantity]), type nullable number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Requirement Type", each "Requirement DOD", type text),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Pn", "Plant", "Requirement Date", "Requirement Type", "Requirement Quantity"}),
    #"Appended Query" = Table.Combine({#"Reordered Columns", Table}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Pn", Order.Ascending}, {"Plant", Order.Ascending}, {"Requirement Date", Order.Ascending}, {"Requirement Type", Order.Ascending}})
in
    #"Sorted Rows"