How to create a stacked bar containing top 5 sales and others?

[shriramvbi]

Hi All,

I'm new to Power BI. I have a stacked graph like this below,

The visual shows the total sales across different countries and the cities acts like stacks. My requirement is to regroup this stack into two categories - Top 5 city sales as one group, and the rest as a separate group.

Example- US consists of cities like NY, LA and many others. I need to group the top 5 cities in the US such as NY, LA and 3 other cities in one group, the other cities that do not fall in the top 5 should be in a separate group named "Others" (Like the below chart). The report should show data for all the countries like this.

I have attached the link to the PBIX for your reference. Thanks to you in advance!

PBIX file link - Download PBIX file here 

With regards,

Shriram

[v-zhangti · Accepted solution]

Hi, @shriramvbi 

You can try the following methods.

Column:

Sales = CALCULATE (Orders[Sales_DAX])

Rank = RANKX('Pseudo Country Table',[Sales])

Category = IF([Rank]<=5,"Top 5 city sales","Other city sales")

Sum sales =
CALCULATE (
    SUM ( 'Pseudo Country Table'[Sales] ),
    FILTER (
        'Pseudo Country Table',
        [Category] = EARLIER ( 'Pseudo Country Table'[Category] )
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.