Forum Discussion
Quartiles and average per quartile
Hi,
I need some help with creating rank and quartiles based on the result of a measure. I then need to be able to list the average result for each quartile.
I have included a (very oversimplified) sample file https://1drv.ms/u/s!AnRGj-TQo1jAg-MNMTUq62UGVjTcSw?e=df6yJa
The output I need is twofold:
1. The Quartile of Conversion by employee:
| Employee Name | Conversion (Measure) | Rank | Quartile |
| Employee 19 | 92.0% | 1 | 1 |
| Employee 17 | 84.7% | 2 | 1 |
| Employee 02 | 79.7% | 3 | 1 |
| Employee 14 | 79.2% | 4 | 1 |
| Employee 05 | 78.0% | 5 | 1 |
| Employee 18 | 77.8% | 6 | 2 |
| Employee 08 | 77.4% | 7 | 2 |
| Employee 15 | 76.2% | 8 | 2 |
| Employee 11 | 74.9% | 9 | 2 |
| Employee 10 | 74.7% | 10 | 2 |
| Employee 04 | 73.1% | 11 | 3 |
| Employee 06 | 72.5% | 12 | 3 |
| Employee 12 | 69.7% | 13 | 3 |
| Employee 13 | 68.2% | 14 | 3 |
| Employee 01 | 67.9% | 15 | 3 |
| Employee 03 | 66.4% | 16 | 4 |
| Employee 20 | 64.3% | 17 | 4 |
| Employee 09 | 62.2% | 18 | 4 |
| Employee 16 | 60.2% | 19 | 4 |
| Employee 07 | 56.1% | 20 | 4 |
and then the average conversion for each quartile:
| Quartile | Average Conversion |
| 1 | 82.7% |
| 2 | 76.2% |
| 3 | 70.3% |
| 4 | 61.8% |
I am going round in circles, I can't quite get quartiles to display correctly using PERCENTILE.INC() and I don't know where to start with the average conversion per quartile!
Any help would be greatly appreciated. It's driving me nuts!
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.⚡
5 Replies
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 Thank you so much, I think you cracked it for me. I will have a go at translating this into my more complicated model, wish me luck and thanks again!
It's been a bit of time since this last post, but what if you wanted to turn this into a bar chart. Similar to the below. Is this even possible?
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!
