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,
My previous reply (and this one) might be a bit delayed to you because they undergo moderation before they become visible to you (maybe because I'm new here).
You're most welcome. Thank you so much once again for all the time and effort you put into helping me. I greatly appreciate it and you genuinely did solve my problem.
When you see my previous reply, I'm just having some trouble creating the line chart. I haven't created one before from a dynamic (formula-based) PivotTable before so I'm a bit confused on the table range to select for the line chart. If you can please guide me on this, I'll greatly appreciate it. Then, I will be able to confirm the accuracy of the report. But, so far so good 👍.
Kind regards,
YP
Built the chart by adding a few formulas to the CALC_SHEET:
To check accuracy, built sheet CHECK with a PivotTable from the Data Model. The 2 slicers from the REPORT sheet are also connected to this PivotTable. Below the PivotTable there's a refence to dynamic array 'Top5Pivoted' from the CALC_SHEET
With the slicers connected to the PivotTable, when you're on the CHECK sheet you can use the PivotTable Region & Year filters instead of:
- Switching to the REPORT sheet
- Selecting items on the slicers
- Switching back to the CHECK sheet
Cheers
Lz.
Hi Lorenzo,
Thank you so much, this works perfectly. And thank you so much for creating the chart and for explaining what you've done. I truly appreciate all your time and efforts and I'm so sorry for all the trouble I may have put you through.
This little exercise actually inspires me to delve more deeper and learn the more advanced stuff in Excel.
Thank you so much once again and have a great weekend.
Cheers,
YP