Grouping Data into Other Category

[Anonymous]

I have a set of data and I want to create a bar chart that returns the top 5 values and groups the rest into an "other" category.

How would I do this?

[cowboyracer]

@v-gizhi-msft 

Hi!

How does this, if it does at all, work for non values?

Say, we have a table with all the houses in town.
One column is the type of house and there are 35 different types of houses.

But 10 of them only occur once or twice.

I want to group them into a class "Other"

Can this be done too?

Kind regards,

Simone

[v-gizhi-msft · Accepted solution]

Hi,

Please try to create a rank column instead of rank measure:

Rank-Column = RANKX('Table','Table'[Revenue],,DESC,Dense)

Then create a new category column:

New Category = IF([Rank-Column]<=5,'Table'[Category],"Other")

Choose this [new Category] and original [Revenue] as a bar chart, it shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[Anonymous]

Hi Giotto,

I am having the same problem. I figured out with your solution that the "Rank-Column" should give me a ranking of all the items on my list from 1 to 5. But when I copied and pasted the code you gave and changed the field name to match my project, it didn't work. First the program came up with a column called "Sum of Rank-Column" that was all 1's. Then when I told it not to summarize, it said it "Can't display the visual".

[amitchandak]

@Anonymous ,For Rank Refer these links - This will how to get it. Specially the first one
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale