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.
Attached is a very simplified version of your Brands report. No more dependency on the Data Model, CUBE/MDX formulas… Instead :
- Slicers are connected to TableSource
- TableSource has an hidden [NOT Filtered] column with formula that indicates if a row is filtered according to the slicer selections
- Above column is used as the filter_array arg. of the PIVOTBY formula. Using PERCENTOF (instead of SUM) as function & setting the appropriate col_sort_order allows sorting as expected & calculating the percentages as a single pass. The grouping the other Brands - if any - is done/calculated from the same array
// TOPN_BRANDS_REPORT
=LAMBDA([top_n],
LET(
TOPN_Value, IF( ISOMITTED( top_n ), TOPN_Default, top_n ),
pivotedBrands, PIVOTBY(
TableSource[Year],
TableSource[Brand],
TableSource[Values Month],
PERCENTOF, 0, 0, 1, 0, -2,
TableSource[NOT_Filtered], 1
),
IF( COLUMNS( pivotedBrands ) -1 >= TOPN_Value,
LET(
arrTopn, CHOOSECOLS( pivotedBrands, SEQUENCE(, TOPN_Value, 2 ) ),
TopnValues, DROP( arrTopn, 1 ),
OtherValues, BYROW( TopnValues, LAMBDA(rw, 1 - SUM( rw ) ) ),
GroupedBrands,
VSTACK(
HSTACK( TAKE( arrTopn, 1 ), GROUPED_BrandsName ),
HSTACK( TopnValues, OtherValues ) ),
HSTACK( TAKE( pivotedBrands,, 1 ), GroupedBrands )
),
pivotedBrands
)
)
)Remains the challenge of the Chart I couldn't make fully dynamic (cf. Chart from dynamic array challenge | Microsoft Community Hub), hence the 'CHART' helper sheet in the workbook. If by any chance someone finds a solution to this issue I'll let you know...
Cheers
Lz.
Thank you so much Lorenzo for really going above and beyond in helping me with this Excel issue. The level of detail, patience, and effort you put into exploring this solution really stands out. Even though this problem is tricky, your dedication made a huge difference and gave me new insights I wouldn’t have found on my own. I truly appreciate the time you invested, it means a lot!
