cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jodes Regular Visitor
Regular Visitor

Top 5 and "others" filter

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")
 
 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Top 5 and "others" filter

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Super User
Super User

Re: Top 5 and "others" filter

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Jodes Regular Visitor
Regular Visitor

Re: Top 5 and "others" filter

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

Community Support Team
Community Support Team

Re: Top 5 and "others" filter

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Top 5 and "others" filter

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Jodes Regular Visitor
Regular Visitor

Re: Top 5 and "others" filter

@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?