Solved

Excel - Show Top 5 Pivot for Current Year Not Total

Posted on 2016-08-05
16
28 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 14

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 14

Expert Comment

by:Edward Pamias
ID: 41744753
Can you give me an example of how it should look?
0
 
LVL 14

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 28

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 14

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 14

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 14

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 14

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 28

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now