Pivot Table Multi-filter Ranking

Question

This may turn into a long winded Q so I apologies in advanced. I'm doing some work for a sports league & need some help with advanced Pivot Table help.

I'm trying to produce a "Historical Record" spreadsheet for the league. I am able to get my required data into a suitable pivot table (See Image 1). The pivot table has two rows these are Name & Team. The team row is used for a future section (Explained Later)

Image 1 - Sample Data in Pivot Table

This data then needs to be sorted in two separate ways. The first sorting version is to sum the total value of several columns and place them into a separate column on an alternative sheet. I have used this formula to start to achieve this:

=QUERY('Player Records - Offense'!$A:$H,"select A, max(D) group by A order by max(D) desc limit 10 label max(D) ''",0)

This sorts through the specified columns in the pivot table and finds the top 10 values an orders them in from highest to lowest with the corresponding name, see Image 2.

Image 2 - Formula Example

The first issue is seen here, as you can see in Image 2 the formula brings through both the individual records as well as the total records for each given player. So is there a way to filter so that only the total values are used in the formula & not each player individual records? Can this filter be customized so that it can filtered based on the team that they played for, i.e. KC / NOR / SA etc.

https://docs.google.com/spreadsheets/d/10R9PsckIoDj-0hMrVQOmd33zBImUbdx5B5NBluXap-U/edit?usp=sharing

Above you will find the link to the sheet, hopefully that may help.

I would really like to try and avoid using a macro on this if possible with the aim of keeping the sheet as dynamic as possible. Though if it's only possible that way then so be it. Thank you for any help you can provide me.

Answer 1

for totals only you can do:

=QUERY('Player Records - Offense'!$A:$H,
 "select A, max(D) where A contains 'Total' group by A 
  order by max(D) desc limit 10 label max(D)''", 0)

and KC can be:

=QUERY('Player Records - Offense'!$A:$H,
 "select A, max(D) where B = 'KC' group by A 
  order by max(D) desc limit 10 label max(D)''", 0)