How to find top N and remaining to be others

[Anonymous]

Hi Team,

From the following data i need to create a top 7 sales items and remaining should be keep it as others.Please let me know what is the formula to calcualte this.

Item	Sales
pepsi	25000
coc	15000
macintosh	5800
cadburry	2500
7up	1500
dew	4500
expresso	6900
oreo	4500
Almond t	450
cool bites	1250
abc bis	4560

Expected output

pepsi	25000
coc	15000
expresso	6900
macintosh	5800
abc bis	4560
dew	4500
oreo	4500
others	5700

[Stachu · Accepted solution]

the only way I know to do this is to create a calculated column, grouping the non top 7 into 'others' bucket

Item2 = 
VAR ItemRank = RANK.EQ('Table'[Sales],'Table'[Sales])
RETURN
IF(ItemRank<=7,'Table'[Item],"other")

EDIT - syntax 

[Anonymous]

@Stachu thank you for the reply.I just got it by using the same lofic by using the RANKX function,

Thank you.