power query average using multiple criteria and grouping

[KullyR]

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 search an answer for the last couple of days and now really need some help!

The result I am looking for is the average compa-ratio for all new hires in the same job and country.

The fields I have as part of the criteria are:

Compa-Ratio (what I want to average)

I want to average by each of these criteria:

"Country & 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"

My current excel formula is this:

'IF(AND([@[Compa Ratio - Base]]>0,[@[New Hire]]="New Hire"),AVERAGEIFS([Compa Ratio - Base],[Country and Job Code],[@[Country and Job Code]],[New Hire],"New Hire"),"")

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 & Job Code"

=AVERAGEX(

FILTER('mytable',

'mytable'[New Hire] = "New Hire" && NOT(ISBLANK([Compa Ratio - Base]))

),[Compa Ratio - Base]

)

Any help would be much appreciated.

[KullyR]

Thank you Gao.  As I am so new to this, the above code is a lot for me to understand.   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")?

    if [New Hire] = "New Hire" and [#"Compa Ratio"] <> "" and Number.From([#"Compa Ratio"]) > 0 then
    List.Average(Table.SelectRows[#"Job Code & Country"]=[#"Job Code & Country"] and [New Hire]="New Hire")        [#"Compa Ratio"] else null

Then the last part of your code would be formatting this custom column to percentage?

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).  Could you please advise further on this?

[Anonymous]

Hi @KullyR ，

Here will rely on the previous step to calculate, you need to replace the Changed Type in the code according to your actual situation, also need to pay attention to the need to be case-sensitive.

Power BI Forum Help: How to integrate M-code into ... - Microsoft Fabric Community
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

[KullyR]

Thank you Gao.  Sorry for the late reply, I was off work for a couple of days.

In my live version, I have a lot more steps and I adjusted my formula in the custom column as per your message and made sure my previous step was stated correctly (in my case it was Changed Type4) but I still got the same error. 

When I try it in the example file I had attached, it works fine so I am not sure what I am doing incorreclty in my actual live version

[lbendlin]

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",  (k)=> if k[New Hire]="New Hire" then  List.Average(Table.SelectRows(#"Changed Type", each [New Hire]="New Hire" and [Compa Ratio]<>null and k[#"Job Code & Country"]=[#"Job Code & Country"])[Compa Ratio]) else null, Percentage.Type)
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[KullyR]

Thank you Ibendlin.  Gao and you both gave me this code and I just wanted to understand it in case I need to edit or create a similar calculated column again.  Is this begining part of the code always the same or does it differ depending on the query/table that I am in?  (I am proficient in Excel, but very new new to the data model and DAX)

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)

This is why I was looking to try and add a formula as a custom column so I can at least try to understand it when I have to edit it

[lbendlin]

That code represents a compressed version of the sample data. Throw it away when you swap in your actual data source.

[lbendlin]

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-...

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/1447...

[KullyR]

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]

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