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.
… cont.
IMHO there's something inconvenient with a Line Chart when you filter on a single year: you can't compare the Brands as the Chart shows nothing on the Plot area - a Bar Chart does
The attached version is the same as the previous one but displays a Bar Chart when there's only 1 Year reported, a Line Chart otherwise. This is done in following the demo. on Show or hide a Chart based on a condition. The 2 Charts (Bar / Line) are on hidden sheet 'HIDDEN_CHARTS'
Cheers
Lz.
Hi Lorenzo,
Lorenzo wrote:
Nevertheless the approach works in your case as you're only concerned by the TOP 5 & Grouped Brands. So the report array always has max. 7 columns (1 for year(s) + 5 Top brands + 1 Grouped brands)
Cont. from previous reply... In terms of this ☝️, my goal wasn't to use years as a slicer at all. Because, as you also discovered 👇
you can't compare the Brands as the Chart shows nothing on the Plot area
which is true. The years was only for the chart to show a time period, not to filter or slice. Therefore, I just wanted to filter/slice by Region and maybe Subcategory so that we could see the market share trend over time for a specific subcategory/all subcategories across a specific region/all regions.
I'm so sorry for not clarifying this earlier. I was wondering why you initially had years in the slicer all along. I didn't know this is what you were trying to show me (as you showed in the bar chart where we're comparing Top 5 vs. other brands for a specific year selected) - not in this case, so that won't be necessary.
The goal here is to just show how the brands market share evolved over a period of time (years) per region/all regions (slicer) and/or subcategory/all subcategory (slicer).
Hope this helps and my sincere apologies again.
Kind regards,
YP