Forum Discussion

KullyR's avatar
KullyR
Regular Visitor
2 years ago

power query average using multiple criteria and grouping

Hello,   I am new to power query and the data model function in Excel and need some help please to try and translate an Excel formula into DAX formula which I can add to my table.  I have tried to ...
Need Help
KullyR's avatar
KullyR
Regular Visitor
2 years ago

Thank you.  I have attached a link to the file showing some sample data.  The end result is in an excel formula in column G which shows the average 'Compa Ratio' for each 'Job Code & Country' where the employee is a 'New Hire'.

 

Column D has the grouping I want to calculate the average by

The yellow highlighted fields are all the columns that are included within my conditions

 

PQ Average CR example 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi KullyR ,

Please refer to the following steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZVBa8JAEIX/igR687Azk012zyXUttBbT+JBMJRAjWADtv++1iQ6rq9Uh57CJrzHm33fbubzrKocZdPs6d4dnq9t09WryXPTvq026/HDxVuScLd/VJ/NR7d/mS2mByc2OBH9OL3Uu8ms2dajkyinx275/jWuxwVxjnS5IQELmsUrp4d6u162xwynJXFE2sKQwgc0T2lwciXKFAYBX8zDah5ipI1Kq9vgYxveAx05pYPpGfAAncjg1HeT7CixJRMihOTfnAZq/W8te3QCC+TkldNZy15TS4haKq7RRnTsqDQM4GCIYHDq4UszRYNTgeDjAWNJ8JcT/kJIR0qHEggARNA1wGxwYocyiXI6K1nUNRByJM1vDxEETeP/3E+HKOPi9gARnRIuDUboYuRg2BJYcLymlj5DIhVnGAZhLgZcI7rORNOK+4U/fRFDAAS5GEgll2zu4hs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, #"Job Code" = _t, Country = _t, #"Job Code & Country" = _t, #"Compa Ratio" = _t, #"New Hire" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Job Code", type text}, {"Country", type text}, {"Job Code & Country", type text}, {"Compa Ratio", Percentage.Type}, {"New Hire", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "New Hire Avg CR", each if [New Hire] = "New Hire" and [Compa Ratio] <> "" and Number.From([Compa Ratio]) > 0 then List.Average(Table.SelectRows(#"Changed Type", (x)=> x[#"Job Code & Country"]=[#"Job Code & Country"] and x[New Hire]="New Hire")[Compa Ratio]) else null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"New Hire Avg CR", Percentage.Type}})
in
    #"Changed Type1"

Best Regards,
Gao
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group