12 Replies Latest reply on Oct 29, 2014 1:07 PM by Diane Barbee

# Summing Average Rankings of Teams by Group

I have a sales floor that is broken into Teams (Blue, Red, Green, etc.). Each individual is ranked on 3 individual criteria (# of Docs sent out, # of Docs returned, # of clients enrolled) and then those 3 are combined to get an "Average Rank" overall. The calc I use for that is basic ([Rank of Docs Out]+[Rank of Resolution Units]+[Rank of Units ])/3 and all of that seems to work fine. I have the individual ranking of each individual in each category and then the average ranking of the 3 in the 4th column. The problem comes in as I group the individuals by their teams. I want to get a subtotal for each team that calculates the average ranking of all individuals on the team. If I use the subtotal feature it RANKS the teams on totals with the highest number getting the 1st ranking but that doesn't work! Below is an example of what I'm trying to accomplish. How do I find the "Averange Rank of Each Team" - meaning (4.67+15+17.33+21.67+37.33+36.67+42)/7 and so on?? If I try to subtotal it just puts 1, 2, 3, etc.

 Team Employee Rank of Docs Out along Team, Employee Name Rank of Resolution Units along Team, Employee Name Rank of Units  along Team, Employee Name Rank Average along Team, Employee Name Black Jane 9.00 4.00 1.00 4.67 Joe 12.00 29.00 4.00 15.00 Robert 40.00 2.00 10.00 17.33 Jordan 16.00 42.00 7.00 21.67 Michael 38.00 27.00 47.00 37.33 Vanessa 23.00 50.00 37.00 36.67 Diane 29.00 51.00 46.00 42.00 Blue Leah 16.00 3.00 7.00 8.67 Liz 5.00 7.00 17.00 9.67 Brent 13.00 10.00 17.00 13.33 Kaleigh 42.00 5.00 23.00 23.33 Callie 22.00 44.00 37.00 34.33 Sara 43.00 44.00 37.00 41.33 Sandy 46.00 42.00 47.00 45.00 Gold Heather 2.00 1.00 3.00 2.00 Nancy 23.00 5.00 12.00 13.33 Jamal 10.00 16.00 26.00 17.33 Ronald 49.00 19.00 7.00 25.00 Kevin 33.00 29.00 33.00 31.67 Jessica 29.00 29.00 37.00 31.67
• ###### 1. Re: Summing Average Rankings of Teams by Group

Could you post a packaged workbook?

• ###### 2. Re: Summing Average Rankings of Teams by Group

You can add all subtotals by going to the 'analysis' tab. Once you do that, on the marks table right click on the measure name and change it to measure(average).

• ###### 3. Re: Summing Average Rankings of Teams by Group

If this is what you are looking for Pooja is on the right track except you want to compute your subtotal as a average by doing so :

• ###### 4. Re: Summing Average Rankings of Teams by Group

Unfortunately I'm not able to package workbook

• ###### 5. Re: Summing Average Rankings of Teams by Group

Yes, Grayson is spot on. How do I copy my screen image to retain all the selection? Usually when I use printscreen or snipping tool, it loses my selection and I am only able to copy the workbook.

• ###### 6. Re: Summing Average Rankings of Teams by Group

When I click on "Analysis, Totals, Total all using (and then it doesn't matter what I pick because all of them give me the same result)" they all give me a "rank" of some sort. The total for the averages (as listed above) (4.67+15+17.33+21.67+37.33+36.67+42) is giving me a "Total" of 3. I know that is not correct. Therefore, using the "subtotal" or "total" feature doesn't work

• ###### 7. Re: Summing Average Rankings of Teams by Group

I do it like this with my snipping tool

• ###### 8. Re: Summing Average Rankings of Teams by Group

Awesome!! Thanks so much, you saved me so much typing time

• ###### 9. Re: Summing Average Rankings of Teams by Group

You can post a packaged workbook even if you have confidential data if you do what I suggested in another thread.  See here: Re: combine two worksheets into a single viz

• ###### 10. Re: Summing Average Rankings of Teams by Group

Here's a screenshot of what I'm talking about...

• ###### 11. Re: Summing Average Rankings of Teams by Group

Not really sure about whats going on. It totally works for me.

• ###### 12. Re: Summing Average Rankings of Teams by Group

I tried doing the packaged worksheet like you said, but when I replace the data source all my custom calculations go away