Forum Discussion
Quartiles and average per quartile
Anonymous I never did this before but I gave it a shot, solution is attached, you can tweak it as you see fit.
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
parry2k I think I have a problem that I don't know how to resolve. My actual data is much more complicated and cannot be shared. I'm having to use ALLSELECTED, as I have a filterable list of employees, not just ALL. In the Rank, this works perfectly.
For some reason, using that same ALLSELECTED to replace ALL within PERCENTILEX.INC, using your DAX means that all the 0.25, 0.5 amd 0.75 comes out to the same value - the highest value of Rank and therefore the Quartile result is 1 for everyone.
My suspision is that ALLSELECTED is changing the context to the row, when it's used in a VAR in this way, and I cannot figure out how to change that. Any ideas?
I actually think I cracked this myself. I have 2 columns in my employee table:
| Employee Name | Employee Number |
| John Smith | 001 |
| Jane Doe | 002 |
| Joe Bloggs | 003 |
| John Smith | 004 |
Originally I had a problem where I had multiple employees with the same names, so had added 2 calculated columns, 1 to count the instances of that employee name and the other to concatenate the name and employee number if there were more than 2 people with the same name, I figured a calculated column would be fine for only a few hundred rows of data:
| Calculated Column | Calculated Column | ||
| Employee Name | Employee Number | Name Count | New Employee Name |
| John Smith | 001 | 2 | John Smith (001) |
| Jane Doe | 002 | 1 | Jane Doe |
| Joe Bloggs | 003 | 1 | Joe Bloggs |
| John Smith | 004 | 2 | John Smith (004) |
Basing my PERCENTILEX.INC using HASONEVALUE on the New Employee Name Calculated column just would not work - as soon as I moved the Name Count / New Employee Name logic into Power Query, it worked a treat!