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.
On more reflection not sure there's a way to do it with Power Pivot:
- Measures can't be put on PivotTables Filters/Rows/Columns areas
- With calculated columns - similar to what you did on sheet with your [Rank] & [Brand Category] columns - it's probable the Ranking won't always be accurate depending on Slicer selections
Though, Power Pivot can help:
- Loaded your table (renamed it TableSource for clarity in formulas) to the Data Model
- Created a basic measure: Total Amount:=SUM( TableSource[Values Month] )
- Added Slicers "Region" & "Year" from the Data Model
1st approach (CUBE_BRANDS) is mainly based on CUBE/MDX formulas. It works but Excel engine is too efficient not waiting for the CUBE formulas to return their results. Net result ==> #GETTING DATA errors most of the time.
2nd approach (CUBE365_BRANDS) is a mix of CUBE/MDX and 365 formulas with helper sheets:
- The few CUBE formulas are required to get the Slicer selections
- Implemented the "Region" & "Year" Slicers only. Adding "Subcategory" is doable
- No fall back check anywhere. Ex.: if - based on data/slicers selection - there's less than 5 Brands to report you'll likely get errors
- Identification of the TOP 5 Brands is based on their Grand Total, according to the filtered [Year]s & [Region]s. Easy to change according to ALL [Year]s & ALL [Region]s in TableSource
- Figures as displayed as % of Row Total, as in the file you shared
Hope this helps a bit. Please double check the results and let me know if something's wrong/inaccurate
Cheers
Lz.
EDIT Attachment CUBE_BRANDS didn't persist (a random known issue on this site/forum). If interested let me know and I'll share it with OneDrive