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 Lorenzo,
I see what you mean. 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?
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 Lorenzo,
No worries, in fact I should have been more explicit in my description.
Thank you so much for taking the time to investigate this and for implementing this workaround. I really appreciate the effort you put into testing different options. I understand the limitation with the legend order, and I’m fine with the ranking prefix solution you’ve applied. It makes the output clear and consistent, and the added flexibility with the Pivot approach is a great benefit.
Thank you so much again for your support, this works well for my needs.
NB: I'm going to update this most recent approach as the marked solution. You deserve it! 🙂
Best regards,
YP
Hi Anonymous29007 / YP
I'm glad you opted for the PivotTable/PivotChart option
- It would be smart of you to Like this post where Riny confirmed this will work w/o Refreshing once the upcoming Auto Refresh PivotTable functionality will be released
- Thanks for updating the marked solution - very few people do this - when the provide feedback... - Definitively smart of you 👍
Am afraid to say you'll have to redo #2 in a moment as I found a solution to order the Brands & always push "Other Brands" at the end w/o a numeric prefix. I just need to clean a couple of things & re-check a last time - should be avail. in an hour or saw...
Hi Lorenzo,
- Thank you for mentioning this, I just went over there to give Riny a like.
- No problem, I like to give credit where it's due. This will also help others to find the right solution when reading this thread.
- Not a problem at all. Please take your time, I'm not in a hurry for this at all.