Top 5 and "others" filter

[Jodes]

I have charts that show market share. But there are too many categories in each chart (because there are so many developers). I need to trim it down to display only the top 5. But it should also show "Other."

The chart should show the Top 5 Starts by Developer (my data is real estate).  I did a search of this topic but I could not seem to get it to work. Let me know if you need any other information.

Thanks.

Edit:

Here are my formulas but I'm getting this error: "Expressions that yield variant data-type cannot be used to define calculated columns."

Rank = RANKX(FILTER('All Data', 'All Data'[Year] = EARLIER('All Data'[Year])), 'All Data'[Starts],,DESC,Dense)

Top 5 = IF('All Data'[Rank] <=5, 'All Data'[Starts], "Others")

[TomMartens]

Hey,

maybe this blogpost may provide you some ideas: https://www.minceddata.info/2018/06/06/topsomething-and-all-the-rest-called-other/

Regards,

Tom

[Jodes]

@TomMartens Thanks Tom. In the blog the link to the .pbx file does not seem to work. It only lets me download a jpeg.

[v-cherch-msft · Accepted solution]

Hi @Jodes 

It seems the column [Starts] cannot be used in your formula.Please check which column you want to be ranked and then use it instead of the [Starts].Below is the similar post for your reference.If you need further help,please share some sample data and expected output.

https://community.powerbi.com/t5/Desktop/TOP-N-and-quot-OTHERS-quot-values/m-p/574191#M271241

Rank = RANKX(FILTER('All Data', 'All Data'[Year] = EARLIER('All Data'[Year])), 'All Data'[Starts],,DESC,Dense)

Regards,

[TomMartens]

Hey @Jodes ,

thanks for the hint that the link is broken, I just updated the blog post.

And here is the direct link to the pbix file:

https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/ETd9RPditutAnMCKhJi8qIEB1p5yB...

Regards,

Tom

[Jodes]

@v-cherch-msft I got it working, thanks.

Is there a simple way to add a slicer so I can select top 5, top 10, etc?