• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Top Ten Based on Two Variables

Cindy Irvin

Cindy Irvin

New Member
Hello, I have a summary worksheet which includes a section that simply returns the first ten rows from a pivot on a different worksheet. The pivot table is highlighting cities that need improvement in service. Due to improvements in performance, some of the 'top ten' by volume have performance >=90% and need to be excluded from the summary page. Is there a way to build a formula to provide the top ten by volume where performance is <90%? Thank you for your help, Cindy
 
Is it possible to sort based on two different values in the pivot? The pivot table is currently sorted to show the cities with the highest volume first. I believe upper management would be okay with not seeing the high performing cities.
 
If you put performance in either the Page or Row area, you should be able to filter on it. Then the only items in Data area would match your criteria I believe.
 
Good idea; however, the performance value is an average of a column that has '1' for on time and '0' for late. The pivot table displays a percentage based on the average of the column (with a goal of having >=90%). Which is what led me to think that perhaps I could create a formula on the 'Summary' worksheet to display the top ten where performance was <90%. Right now, we are manually adjusting the top ten when there are cities with >=90% performance. Thank you for your help.
 
I'm afraid I didn't get that last part. Would it be possible for you to upload an example workbook, showing what data you have, and the layout of your PT?
 
I will work on that today. I've got a couple of other report requests that take priority, then I'll work on an example workbook. Thanks.
 
On your PT, select filter dropdown for Origin City. Select Value Filters - Less than
upload_2014-6-25_15-18-46.png

Change the first dropdown to say GrossOTP%, and then input your cut-off
upload_2014-6-25_15-19-22.png

OriginCity is still sorted, and now it's filtered. Your summary tab displays the correct top 10.
 
You must log in or register to reply here.
Back
Top