Calculate Top Quartile with filter

[Anonymous]

Hi All, 

I am trying to calculate the Top Quartile of data with a filter applied. 

A subset of data looks like this: 

Year	School	Responses	Q5
2019	School 1	9416	88.22
2019	School 2	9099	88.38
2019	School 3	7778	92.33
2019	School 4	7572	86.31
2019	School 5	7551	88.83
2019	School 6	6531	85.17
2019	School 7	5919	86.00
2019	School 8	5870	91.05
2019	School 9	5036	89.00
2019	School 10	4501	88.23
2019	School 11	4113	87.60
2019	School 12	4025	88.50
2019	School 13	3872	90.62
2019	School 14	3629	92.41
2019	School 15	3387	86.24
2019	School 16	3184	90.53
2019	School 17	2979	93.45
2019	School 18	98	78.59
2019	School 19	97	94.47
2019	School 20	90	88.77
2019	School 21	47	81.81
2019	School 22	45	80.32
2019	School 23	34	89.49

I am using the following formula

Top_Quartile_School = 
    PERCENTILEX.INC(
    ALLSELECTED(Table[School]),
        [Q5],
        .75)

This works generally, however I need to be able to filter and calculate the Top Quartile where responses >100 and I cant seem to build this into the measure and get it to work.

Notes:

I need to do this as a measure rather than create another table as I have other dashboard filters. 

Both Responses and Q5 are measures

I have had a look at this thread: https://community.powerbi.com/t5/Desktop/PERCENTILE-inside-CALCULATE-does-not-anymore-recognise-data... but I cant quite figure out how to get it to work. 

Thanks for any help

[v-gizhi-msft]

Hi,

According to your description, i create a simple table to test:

Responses = SUM(School[Res])

Q5 = SUM(School[Students])/CALCULATE(SUM(School[Students]),ALLSELECTED(School))

Please try this measure:

Top_Quartile_School = RANKX(GROUPBY(ALLSELECTED('School'),'School'[Year],School[School]),[Q5],CALCULATE([Q5]))

Then apply filter to the table visual, and it shows schools' rank:

Later, you can filter any number of top rank you want.

Hope this helps.

Best Regards,

Giotto Zhi

[Anonymous]

Thanks @v-gizhi-msft  this is great and I will most certainly use this somewhere in the report. 

I want to be able to calculate the top quartile of Q5 records with more than 100 responses dynamically if possible though as I need to create a visual (a lolipop graph) that shows the gap to the top quartile for each school. Is there a way to do this with percentile.inc?

[amitchandak]

Refer, if these can help

https://community.powerbi.com/t5/Desktop/Dynamic-Quartiles/m-p/814004

https://community.powerbi.com/t5/Desktop/Matrix-quartiles-percentiles/m-p/874703

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous]

Thanks @amitchandak neither of those solutions are applicable I dont think. I want to handle it within a measure and filter to >100 responses.