Percentile of multiple filtered data

[Anonymous]

Firstly apologies if this is not clear - first time poster (and very new user!)

I have some data (example below) that I am trying to get upper and lower quartiles grouped by person.

I've got a matrix table set up which has filters for Group and Type, then I've got a measure set up to calculate the Average, Q1 and Q3 for each person's sales. This works fine when just one type is selected, but if I select more than one type the average still works but the quartile calculations are wrong. It seems to be doing something like calculating the quartile for each person and then adding those together to get the final result rather than calculating the quartile by person

Data:

Type	Name	Group	Sales
Apple	Peter	A	177417
Apple	Jane	A	107345
Apple	Mary	A	0
Apple	Steven	A	20960
Apple	Rebecca	A	51420
Apple	Andy	A	24638
Apple	Kate	A	0
Apple	Bev	B	24359
Apple	Shaheen	B	19121
Banana	Peter	A	485971
Banana	Jane	A	77258
Banana	Mary	A	0
Banana	Steven	A	6332
Banana	Rebecca	A	99248
Banana	Andy	A	23550
Banana	Kate	A	0
Banana	Bev	B	44733
Banana	Shaheen	B	260788

Matrix I have generated (All Groups and Types selected in filter):

Name     Sum of Sales

Andy	48188
Bev	69092
Jane	184603
Kate	0
Mary	0
Peter	663388
Rebecca	150668
Shaheen	279909
Steven	27292

Q1 of this data set would be 184603, I'm getting something much lower (sorry can't say exactly what as the above is dummy data).

Is anyone able to help? I've tried various SUMMARIZE and ALLSELECTED options but am really struggling. 

Thank you hugely in advance.

Percentile Measure:

Quartile 1 = VALUE(PERCENTILE.INC('Table'[Sales],0.75))

[Anonymous]

I've just realised that I have the same problem with the Average - I forgot I'd fiddled it as follows:

Average = SUM('Table'[Sales])/DISTINCTCOUNT('Table'[Name])

So, when I add more than one Type together it is taking the average of the two types e.g. for Peter it is averaging Apple and Banana, and then the value I get is the average of those averages. So the same problem as the percentiles.

[Anonymous · Accepted solution]

Solved it - I ended up creating two measures as follows:

Q1 = PERCENTILEX.INC(VALUES(Table[Name]),CALCULATE(SUM(Table[Sales])),0.75)

Average = AVERAGEX(VALUES(Table[Name]),CALCULATE(SUM(Table[Sales])))

[SalmanABDul_97]

Hi Anonymous, 

I'm facing same your problem and your answer helped me, but i need one more thing to achive my goal. 

%75= PERCENTILEX.INC(VALUES(Product[Productname]),CALCULATE(fact[value]),0.75)

it is working but i need to add ALLSELECTED to this function so this work on all the visable table

[v-huizhn-msft]

Hi @Anonymous,

Glad to hear you have resolved your issue, please mark your workaround as answer, so more people will learn things and get helful information clearly.

Best Regards,
Angelia