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!