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.
Playing a bit more with PIVOTBY arguments allows sorting the TOP Brands in Descending order according to their Total. This eliminates a +/- complex & unnecessary calc. step
Updated version attached (crossing fingers it'll persist…)
Lz.
Hi Lorenzo,
I was just typing a reply to your previous response when you sent this one through...
Thank you so much! This (and the previous) solution was exactly what I was looking for. Because, I don't know much about advanced Excel, I didn't even think of this approach.
In your solution, everything looks good, and this is correct from my end. I just have two questions:
- If I was to add a slicer for Subcategory, how would I go about doing this? I noticed in the MDX tab, you had CUBESET formulas for the Region and Year slicers, will the same formula apply for Subcategory?
- How would I create the dynamic line chart from the report tab in the file that you attached?
(No. 2 might sound silly) But, I'm so used to selecting the PivotTable and creating the line chart from that PivotTable. In a more advanced solution like this, I'm a bit lost on how to create the line chart (I don't know which table to select) 😅.
Thank you so much once again. I'm so sorry for all the trouble but, I greatly appreciate all the time and effort that you took to help me 😃.
Regards,
YP