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
lbendlin's avatar
lbendlin
Super User
2 years ago

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

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