Top X in Excel/PowerPivot Pivot Table Filtered by Column Label

2
votes

I'm working on a Powerpivot Pivot Table listing sales for my company. The table looks like this:

Row Label: Client Name Column Label: Year Values: Sales Amount

So something like this (columns are Client Name, 2014, 2013, 2012)

Client 1 | $10,000 | $15,000 | $20,000
Client 2 | $15,000 | $12,000 | $30,000

I'd like the table and the years to be the same but I want to show the top 25 customers only for year 2014. If I tell it to take top 25 by customer sales it gives me top 25 by total sales given all the filters. How can I do this?

1
excelpivot-tablepowerpivot
are you using the filter field in Pivot table or are you filtering rows in pivot table? – Petr Havlik
I'm click row labels->value filters->top 10. And then change the 10 to 25. The catch is that I want to sort by 2014 sales only, but if 2013 sales are selected as well, they appear in a column next to 2013 as they are column labels, and I get the top 25 customers with the most sales in 2014 and 2013 combined. – master_cylinder
so what you want is to display TOP25 customers based on the sales made in 2014, and for those customers also list sales for previous years? – Petr Havlik
Exactly! There is a slicer at the top of the sheet containing years, 2010,2011,2012,2013,2014. Years is a column label. I guess in a perfect world it'd be sorted based on the top 25 of the highest year selected, but given that 98% of the time that'll be 2014 I'm happy with that too. – master_cylinder

1 Answers

1
votes

Master_cylinder,

after thinking about this I suggest creating separates calculated measures (Excel 2010, in 2013 it's Calculated Fields) for each year that will allow easy filtering. It might be tricky if you have way to many years, but this way you can be sure the results are filtered exactly how you need to:

enter image description here

So for each year, create this simple measure:

=CALCULATE([Sum of Sales], Sales[Year]=2014)

For other years, simple change the filter parameter at the end - replace 2014 with different years. Than drag all the measures on the table, so that you will get something like this:

enter image description here

When you are done, simple filter the table using regular filter option and pick those settings:

enter image description here

This will get you the results you need.

PS: This is the powerpivot table structure that I was working with, and link to my source file in Excel (2010):

enter image description here