Forum Discussion
Top n vs. Others in Excel
Hi all,
I'm seeking some help because I'm kind of new to the more intermediate stuff in Excel. I have an Excel table with the following columns: Subcategory in column A, Brand in column B, Region in column C, Year in column D and Values Month in column E. I want to create a PivotTable and a Pivot line chart from this PivotTable that ranks the Top 5 Brands vs. Other Competitors by each region. For added context: There are 5 subcategories, 3 regions and 25 brands.
Currently, I've tried grouping the remaining 20 brands as "Other Competitors" vs. the Top 5 brands within a selected region and possibly all regions (when no selection is made).
I'm seeking a solution similar to this...
Please mind the colours. I will sort those out later. But, the problem that I'm faced with is that upon selection of a region, the PivotTable won't update to the Top 5 brands of a selected region because they've already been grouped. How can I make this more dynamic so that I'm able to show The Top 5 brands vs. Others?
Please help.
EDIT: My operating system is Windows 10 (64-bit) and I use Excel 365 (Desktop version). For reference, I've attached a link to a sample file.
https://1drv.ms/x/c/b2d878e32a062614/IQC1wcnwLICcQasOfnGcwKn0ASjpXp9xQ6rjnOP10Jal5cc?e=HaXEWd
Thank you all once again.
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.
20 Replies
Posted a possible workaround on Chart from dynamic array challenge
I haven't looked at what this would mean in term of implementation with your grouping but that's probably doable... However, if your Grouped Brands have an higher total than any of the Top Brands the sorting in the PivotTable & Chart will reflect that. In other words on the Chart Legend "Other Brands" won't necessarily be the last item and I guess that's not what you expect, right?
Hi Lorenzo,
I see what you mean. 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?
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.
(You're welcome - Smart people deserve smart service and this case is definitively more interesting than anticipated)
Updated Chart from dynamic array challenge and TBH I now doubt there's - with current version - a solution. 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)
In the previous version with the CHART sheet and its Named ranges, when there's not enough data (simulated below) we get a Chart where the Legend is what it is and all the researches I did lead to the same conclusion: there's no way to hide Legend items with no/invalid values
Instead of using Named ranges for the Chart, the attached version generates an array of 7 columns in any case and the Chart is setup from that array in following the bullet points in Chart from dynamic array challenge. #N/A - instead of <blank> - display in the Legend on scenarios like the above one (replacing #N/A with <blank> is doable...):
- The #N/A columns & values are hidden with Conditional Formatting
- The Chart Title is dynamically adjusted to reflect what's displayed
- If you keep the sheet protected you won't see the little triangles on cells with errors
- If you change your mind and want to look at i.e. the TOP 3, see the SETTINGS sheet
Cont. on next post...
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.
… 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
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!
- Thank you for marking as Solved
- You can safely delete Measure [Total Amount] in the Data Model/Power Pivot. It was used to identify the TOP 5 with the CUBE/MDX approach. It's no longer needed with the CUBE365 one. Sorry for not deleting it earlier
- I will appreciate you later confirm the accuracy of the report
Thanks
Lz.Hi Lorenzo,
My previous reply (and this one) might be a bit delayed to you because they undergo moderation before they become visible to you (maybe because I'm new here).
You're most welcome. Thank you so much once again for all the time and effort you put into helping me. I greatly appreciate it and you genuinely did solve my problem.
When you see my previous reply, I'm just having some trouble creating the line chart. I haven't created one before from a dynamic (formula-based) PivotTable before so I'm a bit confused on the table range to select for the line chart. If you can please guide me on this, I'll greatly appreciate it. Then, I will be able to confirm the accuracy of the report. But, so far so good 👍.
Kind regards,
YP
Built the chart by adding a few formulas to the CALC_SHEET:
To check accuracy, built sheet CHECK with a PivotTable from the Data Model. The 2 slicers from the REPORT sheet are also connected to this PivotTable. Below the PivotTable there's a refence to dynamic array 'Top5Pivoted' from the CALC_SHEET
With the slicers connected to the PivotTable, when you're on the CHECK sheet you can use the PivotTable Region & Year filters instead of:
- Switching to the REPORT sheet
- Selecting items on the slicers
- Switching back to the CHECK sheet
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
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
Hi
As I understand & assuming it's doable this would require Power Pivot - not sure & not my expertise 😒 However, I believe you should clarify how you expect to distinguish the TOP 5 (in green below) when several years display, ex.:
- Based on the Average by column/Brand or something else?
- What if the 6th, 7th... value equals the 5th?Hi Lorenzo,
Thank you so much for your assistance, Power Pivot isn't my expertise either but, I do know a little bit.
To answer your question:
I determined the Top 5 brands according to the Total Sum of Values (I dragged Sum of Values Month twice and used the second column for rank after sorting them by descending order). In the screenshot below, when I was looking at this roughly, I determined the Top 5 brands according to their sum of values for each year to give me a grand total for all the years. Now, because I'm trying to show the brands evolution over time per region, I'm looking at the Total Rank which is based on the grand total (Total Sum of Values) for all years.
In addition to this, there was another approach that I did try, and that was to create some helper columns G and H (Rank and Brand Category).
For column G (Rank), I used the following formula:
=SUMPRODUCT((SUMIF([Brand],[Brand],[Values Month])>SUMIF([Brand],[@Brand],[Values Month]))/COUNTIF([Brand],[Brand]))+1For column H (Brand Category), I used this formula:
=IF([@Rank]<=5,[@Brand],"Other Brands")These two columns get me very close to what I'm trying to achieve. I get the correct result when I show all regions (no selection on slicer) and I get the correct result for the Center region. But, for North and South, the Top 3 ranks appear to be correct, but ranks 4 and 5 are wrong. So from here, I really don't know where I've gone wrong 😥.
Hi
2 days with no reply probably means nobody's ready to re-create existing data (takes time), running the risk it doesn't reflect yours ==> Suggest you share your workbook (anonymized) with OneDrive, Google Drive or the like
Also, please mention the version of Excel you run and on which platform (Windows, Mac, Web...)
Hope this all makes senseHi Lorenzo,
Thank you so much for guiding me on this platform. I'm kind of new here and don't quite know how things operate on this forum. I'm not sure if my previous reply to you went through because it isn't reflecting on my end but, I've managed to update my original post to reflect what you've suggested.
Thank you so much once again, I greatly appreciate it!
