Top Percentile by Group

Question

everyone. Can anyone please help me with the following? Any pointers or assistance is appreciated!

I have a subset of the dataset with +500,000 rows that looks like this

|— Group —|— Name —|— Value1 —|— Value2 —|

Within each group, I'm trying to identify the names in the top 5 and top 10 percentile of Value 1, so that I can proceed to compute the sum of value 2 for each of the identified percentiles.

So far I've been able to create a pivot table that looks like this.

           |----------|--Sum Val1--|--Sum Val2--|
|--GroupA--|----------|     Totals for GroupA
|----------|-Name A1--|     Values.......
|----------|-Name A2--|     Values.......
    ...
|----------|-Name An--|     Values.......
|--GroupB--|----------|     Totals for GroupB
    ...                     Values.......
|--GroupZ--|----------|     Totals for GroupZ

I could identify the percentiles manually, but I imagine there is an easier way. I've done several searches on how to proceed, but I only encounter procedures to find percentiles among the whole dataset.

Answer 1

The way your pivot table is setup, applying a top 10 filter to Names will find the top Names within each Group. If you want to find the top Names overall, you have to put the Names field before the Group field.

Here's a mockup I made:

When I filter for Top 2 Names:

When I put Names as the top field and then filter for the Top 2:

---

As suggested in the comments, you could also add helper columns to your original data that calculate each rows impact and then determine if it's in the Top 10 and return TRUE/FALSE. You could then apply a filter to the pivot table to only show those with TRUE in that field.