Solved

Excel - Show Top 5 Pivot for Current Year Not Total

Posted on 2016-08-05
16
35 Views
Last Modified: 2016-09-12
Hi-

I have the attached workbook and would like to show top 5 for the current year not total. For example, in current workbook the top 5 for 2016 is company 4 , company 3, company 5.

However, when doing the top 5 the following are shown: company 2, company 5, company 1.

Can this be done in the pivot?
example_2.xlsx
0
Comment
Question by:kwarden13
  • 7
  • 3
  • 2
16 Comments
 
LVL 16

Expert Comment

by:Edward Pamias
ID: 41744608
Do you want it to look like this??

Client      (All)      
            
Count of Sales      Column Labels      
Row Labels      2016      2015
Company 5      3      3
Company 4      3      
Company 1      3      4
Company 3      3      3
Company 2      2      4
0
 

Author Comment

by:kwarden13
ID: 41744686
Sorry I meant show top 3.

The top 3 by 2016 is different then the total
0
 
LVL 16

Expert Comment

by:Edward Pamias
ID: 41744753
Can you give me an example of how it should look?
0
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 
LVL 16

Expert Comment

by:Edward Pamias
ID: 41744760
I created a table and sorted....  See attached and Table Tab.
EES-of-example_2.xlsx
0
 
LVL 30

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 250 total points
ID: 41745131
I have inserted another pivot table along with a slicer to pick a year. See if this is what you are trying to achieve.
example_2.xlsx
0
 

Author Comment

by:kwarden13
ID: 41747417
Hi Subodh-

This is what I want except now I want to be able to see how they did year over year. So its fine to select the year but how do I compare to 2015?
0
 
LVL 16

Expert Comment

by:Edward Pamias
ID: 41747454
Check out sheet 2. I added the top 3 for 2016 and 2015 and a name range so when you add more data just refresh the pivot and it will up date the top 3.
EE-of-example_2.xlsx
0
 
LVL 16

Assisted Solution

by:Edward Pamias
Edward Pamias earned 250 total points
ID: 41747467
I did one pivot with years side by side.
EE-of-example_3.xlsx
0
 

Author Comment

by:kwarden13
ID: 41747492
I was trying to get it in one pivot. I am guessing that is not doable. In the last example, it shows top 3 by total spend (for years 2015 and 2016). Not just the latest year.

It's odd you can't select which year.
0
 
LVL 16

Expert Comment

by:Edward Pamias
ID: 41747493
What do you mean you were trying to get in one pivot? Once you open the file makes sure you select enable editing.
0
 
LVL 16

Expert Comment

by:Edward Pamias
ID: 41747498
You said you wanted to see side by side examples? So which one did you like better the previous file or the last sample?
0
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41785100
Considering the ask was not doable, the possible alternatives were shown in the Post ID: 41745131 and Post ID: 41747467.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question