Multiple SUMIFS on column

[Anonymous]

Spoiler

 Hello all, 

I try to replicate this formula in DAX but they all give me error message (Sumx, calculate, lookupvalue...), any tips ? 

Here is a picture of why I try to do : 

Thanks a lot 

Charlotte 

[Anonymous]

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

[BA_Pete · Accepted solution]

@Anonymous ,

Great news, happy to help 🙂

Please accept the answer as the solution. This will help others with the same problem to find the solution quicker.

Thanks,

Pete

[BA_Pete]

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