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,
Ah thank you so much! You've truly been such an amazing contributor and sole problem solver on this challenge. I can't thank you enough. This wasn't easy 😅. You've been so willing to help and you persisted until you found better solutions, and that's more than I could have ever asked for. If I could mark more than one approach as a solution, believe me I would. You really deserve it, given all your time and efforts.
Nevertheless, I've truly learnt a lot from you in this challenge and it gave me a newfound interest for advanced Excel 😁.
This is going to be a bit long, but let me take you through my thought process and why I was wanting a solution like the one we've been working on.
In this image (prior to me coming here), the chart originally looked like this, (without any grouping)... Now, this chart looked very cluttered and we wouldn't be able to distinguish or compare any brands visually below 2-3% for example. Even greying out the bottom didn't seem to do much justice here, and it just looked a bit distracting.
The goal for this study was to see how these Shampoo brands market share evolved over time. One specific supplier, has 2 brands (Shinez and Starbust). These 2 brands are most important to me, because I'm doing a Net Revenue Management study for a fictional supplier called HealthMax.
After completing my analysis, I realised that Shinez and Starbust were consistently in the Top 5 brands for each region (Center, North & South) and in all regions (when no filter/slicer selection is made).
To improve the visual and UX, I thought it might be better to show the individual Top 5 brands evolution over time (years) vs. "Other brands" (remaining 20 brands). In this way, we still have the goal in mind except, instead of looking at all 25 brands on a single visual (like the image above), it would make sense to compare HealthMax's (Shinez and Starbust) brands performance against and it's closest competitors brands (HealthMax's brands + Top 3 competitor brands to make Top 5) vs. "other brands" who hold very little to no market share.
Thank you very much for providing the full context. Looking at the chart I better understand what Your challenge was 🤔
The attached option uses the same approach we've been discussing these last days. In summary:
- TableSource is filtered by the Slicer(s)
- PIVOTBY calc. the %age by [Year] & [Brand] relative to the Row Total. Data are sorted DESC according to [Brand] "best Total"
- The formula (below):
- Takes the PIVOTBY resulting array to Group [Brand] above - if any - the TOPN parameter
- Padds the [Brand] left with the appropriate number of Zero Width Space chars. to ensure [Brand] will be ordered according to there rank in the PivotTable & Chart, with [Grouped Brand] always as the last [Brand] whatever its rank is
- Unpivots the data as a single array that's used as PivotTable Data Source
PivotTable:
- [Brand] must be sorted A-Z
- Until MSFT releases the PivotTable Auto Refresh functionality, any change on the Slicer(s) requires a Refresh of the PivotTable
Side effect of the Zero Width Spaces:
- None on screen
- According to my researches none on printing - to be checked though...
- [Brand] on PivotTable/Chart <> TableSource[Brand]*
* I will provide more information on a separate post later
Formula
I did my best to make the variable names as clear as possible and decomposed as much as possible - any question let me know:
// Source_PivotTable
=LET(
TOPN_value, IF( ISBLANK( SETTINGS!TOPN_Default ), 5, SETTINGS!TOPN_Default ),
GROUPED_BrandName, IF( ISBLANK( SETTINGS!TOPN_GROUPED_Names ), "Other Brands", SETTINGS!TOPN_GROUPED_Names ),
pivotedAllBrands,
PIVOTBY(
TableSource[Year], TableSource[Brand], TableSource[Values Month],
PERCENTOF,
0, 0, 1, 0, -2,
TableSource[NOT_Filtered],
1
),
OtherBrand_Exists?, COLUMNS( pivotedAllBrands) -1 > TOPN_value,
groupedOtherBrand_If_Exists,
IF( OtherBrand_Exists?,
LET(
TopnBrand_series, CHOOSECOLS( pivotedAllBrands, SEQUENCE(, TOPN_value, 2 ) ),
TopnBrand_values, DROP( TopnBrand_series, 1 ),
GroupedBrand_values, BYROW( TopnBrand_values, LAMBDA(rw, 1 -SUM(rw) ) ),
AllBrand_series, VSTACK(
HSTACK( TAKE( TopnBrand_series, 1 ), GROUPED_BrandName ),
HSTACK( TopnBrand_values, GroupedBrand_values )
),
AllBrand_series
),
pivotedAllBrands
),
prep_UnPivoting_BEG, "------------------------------------------------------",
LeftCol_ToDrop, --NOT( OtherBrand_Exists? ),
brandNames, DROP( TAKE( groupedOtherBrand_If_Exists, 1 ),, LeftCol_ToDrop ),
brandCount, COLUMNS( brandNames ),
brandRanks_Desc, SEQUENCE(, brandCount, brandCount, -1 ),
paddedBrandNames, REPT( UNICHAR(8203), brandRanks_Desc ) & brandNames,
years, DROP( TAKE( pivotedAllBrands,, 1 ), 1 ),
values, DROP( groupedOtherBrand_If_Exists, 1, LeftCol_ToDrop ),
valuesNotBlank, NOT( ISBLANK( values ) ),
prep_UnPivoting_END, "------------------------------------------------------",
UnPivotedData,
LET(
UnPivot, LAMBDA(array, TOCOL( IF( valuesNotBlank, array ) ) ),
UnPivoted_years, UnPivot( years ),
UnPivoted_brands, UnPivot( paddedBrandNames ),
UnPivoted_values, TOCOL( values ),
HSTACK( UnPivoted_years, UnPivoted_brands, UnPivoted_values )
),
VSTACK( {"Year", "Brand", "Value"}, UnPivotedData )
)Cheers
Lz.