Multiple SUMIFS on column

Hi Anonymous ,

1) In Power Query either go to your sales table or reference your sales table and add this new custom column:

personList = {[director], [manager], [rep]}

2) Expand this column to New Rows

3) Use your new [personList] field with [sales] to get your required output:

Here is the M code that you can copy and paste into a new blank query if you want to see it step-by-step:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY7NCsJADITfJecepNt2fZeyh7BEjWy3un/g25ssCFIQL5kJ84XMusIDa4ABKLEXuSSMnrJH8eMJ3HAEGu+BStF8Mn/yeelA4KxrokgiG5YbUxVnl3n6BiSRmQs15czn/I7+WSlr9AqNqT9JV47dml+M1pIiaq09g3Nv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [director = _t, manager = _t, rep = _t, sales = _t]),
    addPersonList = Table.AddColumn(Source, "personList", each {[director], [manager], [rep]}),
    expandPersonList = Table.ExpandListColumn(addPersonList, "personList"),
    chgAllTypes = Table.TransformColumnTypes(expandPersonList,{{"director", type text}, {"manager", type text}, {"rep", type text}, {"sales", Int64.Type}, {"personList", type text}})
in
    chgAllTypes

Pete

Thank you very much Pete !! This is working perfectly !