Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
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
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?
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
Thanks @amitchandak neither of those solutions are applicable I dont think. I want to handle it within a measure and filter to >100 responses.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
89 | |
86 | |
75 | |
69 | |
68 |
User | Count |
---|---|
220 | |
128 | |
117 | |
82 | |
77 |