Forum Discussion
Top n vs. Others in Excel
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
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 😥.
