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

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

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

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      KullyR
      Regular Visitor
      2 years ago

      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