power query average using multiple criteria and grouping

Question

Hello,&nbsp;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.&nbsp; I have tried to search an answer for the last couple of days and now really need some help!&nbsp;The result I am looking for is the average compa-ratio for all new hires in the same job and country.&nbsp;The fields I have as part of the criteria are:Compa-Ratio (what I want to average)&nbsp;I want to average by each of these criteria:"Country &amp; Job Code" is the grouping i want used for the average result"New Hire" = New Hire"Compa-Ratio - Base" does not = null and does not equal "no data"&nbsp;My current excel formula is this:'IF(AND([@[Compa Ratio - Base]]&gt;0,[@[New Hire]]="New Hire"),AVERAGEIFS([Compa Ratio - Base],[Country and Job Code],[@[Country and Job Code]],[New Hire],"New Hire"),"")&nbsp;I tried to create a measure in Power Query (not sure that is the right way to go or add a calculated field to my table in the PQ view?) but i couldn't figure out how to average this using the grouping for "Country &amp; Job Code"=AVERAGEX(FILTER('mytable','mytable'[New Hire] = "New Hire" &amp;&amp; NOT(ISBLANK([Compa Ratio - Base]))),[Compa Ratio - Base])&nbsp;Any help would be much appreciated.

lbendlin · Answer

lbendlin · Answer

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 · Answer

Thank you.&nbsp; I have attached a link to the file showing some sample data.&nbsp;&nbsp;The end result is in an excel formula in column G which shows the average 'Compa Ratio' for each 'Job Code &amp; Country' where the employee is a 'New Hire'.&nbsp;Column D has the grouping I want to calculate the average byThe yellow highlighted fields are all the columns that are included within my conditions&nbsp;PQ Average CR example&nbsp;

anonymous · Answer

Hi&nbsp;KullyR&nbsp;,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 &amp; 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 &amp; 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] &lt;&gt; "" and Number.From([Compa Ratio]) &gt; 0 then List.Average(Table.SelectRows(#"Changed Type", (x)=&gt; x[#"Job Code &amp; Country"]=[#"Job Code &amp; 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,GaoCommunity Support Team
&nbsp;
If there is any post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;&nbsp;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.&nbsp;Thanks a lot!
How to get your questions answered quickly&nbsp;--&nbsp;&nbsp;How to provide sample data in the Power BI Forum&nbsp;--&nbsp;China Power BI User Group

kullyr · Answer

Thank you Gao.&nbsp; As I am so new to this, the above code is a lot for me to understand.&nbsp; &nbsp;I tried to copy it and add it to a custom column but that did not work.Am I right in thinking that I can add a custom column into my PQ table and add in the middle part of that code which would look like this (named the custom column "New Hire Avg CR")?&nbsp;&nbsp; &nbsp; if [New Hire] = "New Hire" and [#"Compa Ratio"] &lt;&gt; "" and Number.From([#"Compa Ratio"]) &gt; 0 then&nbsp; &nbsp; List.Average(Table.SelectRows[#"Job Code &amp; Country"]=[#"Job Code &amp; Country"] and [New Hire]="New Hire")&nbsp; &nbsp; &nbsp; &nbsp; [#"Compa Ratio"] else null&nbsp;Then the last part of your code would be formatting this custom column to percentage?&nbsp;I have tried it as I described above, and although the formula looked ok in the editor, the average for New Hire is not calculating and is showing as error (the Existing employees are showing a null value which is correct).&nbsp; Could you please advise further on this?

Forum Discussion

Related content

Sequential Order column based on multiple criteria

DAX FILTER with multiple criteria

Multiple Criteria Filter

Dynamic Filter creation based on multiple criteria

Date difference with multiple criteria