Creating "Others" Category with static category, Not Top N

[Shuchita_Jain]

Hi, 

I have a table with 25 categories (Let's just say Apple, Beet, Carrot, Donut, Emu, Fig ....so on). The names are not always going to be same and new name may get added or deleted over the quarters. But I know Apple, Peaches, Grapes and Melon are going to be part of my set and I am only interested to find out the sales data for these categories. I want to show a pie chart or bar chart with only these four categories and rest everything to go in "Others" category. I want a way to keep my fixed category on chart while others can get dynamically updated. How the DAX will work for this?

Thanks for help!

[v-yiruan-msft · Accepted solution]

Hi @Shuchita_Jain ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a calculated column as below:

Category =
IF (
    'Table'[Fruits] IN { "Apple", "Peaches", "Grapes", "Melon" },
    'Table'[Fruits],
    "Others"
)

2. Create a pie chart and apply the category field on the visual

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[ValtteriN]

Hi,

By selecting the "new group" from here:

You can create such group:

Here my test data has 4 different attributes and I grouped "price 1 and Price 2" to a group and other values are in the other group.


Now if I add a new attribute it will automatically be placed in the other group.

E.g. (price 5)

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/