Quartiles and average per quartile

[Anonymous]

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]

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

[Anonymous]

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!

[parry2k · Accepted solution]

@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.⚡

[Anonymous]

@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!