Forum Discussion
Top n vs. Others in Excel
First of all my sincere apologies re. slicer Year, your initial post was very clear re. what you expect.
Ideally, I’d like ‘Other Brands’ fixed at the end of the legend. Do you know if there’s a way to force that order, maybe by customizing the sort or manually adjusting the legend?
That's what I suspected and this makes totally sense - keep focus on the TOPN. I investigated and tested a number of options and found no way to customize/hide items on the Legend.
On the other hand I went ahead and implemented the solution with the workaround I posted on Chart from dynamic array challenge. This gives a PivotTable that looks the same as the array. But again, the issue was to force 'Other Brands' to stay at the end. And whatever I tried I couldn't make it happen w/o prefixing the Brand names with a ranking:Not really nice but this makes things even more clear. The benefit of the Pivot approach is you have no more restrictions re. the TOPN value, it can be any number > 0. The only annoying thing is you'll have to do a Data > Refresh All every time you play with the Slicers until MSFT releases the Pivot Auto Refresh functionality
IMHO this a more robust option and if you want to add a Subcategory Slicer, add it from TableSource - not from the PivotTable - and this should work with no other changes
Hope this all makes sense
Cheers
Lz.
Hi
As I understand & assuming it's doable this would require Power Pivot - not sure & not my expertise 😒 However, I believe you should clarify how you expect to distinguish the TOP 5 (in green below) when several years display, ex.:
- Based on the Average by column/Brand or something else?
- What if the 6th, 7th... value equals the 5th?
Hi Lorenzo,
Thank you so much for your assistance, Power Pivot isn't my expertise either but, I do know a little bit.
To answer your question:
I determined the Top 5 brands according to the Total Sum of Values (I dragged Sum of Values Month twice and used the second column for rank after sorting them by descending order). In the screenshot below, when I was looking at this roughly, I determined the Top 5 brands according to their sum of values for each year to give me a grand total for all the years. Now, because I'm trying to show the brands evolution over time per region, I'm looking at the Total Rank which is based on the grand total (Total Sum of Values) for all years.
In addition to this, there was another approach that I did try, and that was to create some helper columns G and H (Rank and Brand Category).
For column G (Rank), I used the following formula:
=SUMPRODUCT((SUMIF([Brand],[Brand],[Values Month])>SUMIF([Brand],[@Brand],[Values Month]))/COUNTIF([Brand],[Brand]))+1For column H (Brand Category), I used this formula:
=IF([@Rank]<=5,[@Brand],"Other Brands")These two columns get me very close to what I'm trying to achieve. I get the correct result when I show all regions (no selection on slicer) and I get the correct result for the Center region. But, for North and South, the Top 3 ranks appear to be correct, but ranks 4 and 5 are wrong. So from here, I really don't know where I've gone wrong 😥.