Forum Discussion
power query average using multiple criteria and grouping
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?
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
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