"Top 10 + Others" working with filters ?

[François]

I want to display a pie with the top 10 brands we are selling + one slice regrouping all the other brands (11 slides total). 
I successfully did that with the right measures, but it's only working for all our sales. Filtering on a specific year is not working, nor any other filter. 

I can't find a way to make it work. I found several ways to achieve this top N + "others", but it's never working with filters. Since my online researches are failing, I thought that maybe someone here was more clever than me. Any way to do that ? Many thanks for any help ! 🙂

[Kaviraj11]

Hi, you can implement using the solution below:

Step 1

Create a Table, which union all categories from source table and hard coded “Other”

TableName = CALCULATETABLE(UNION(VALUES(SourceTableName[CategoryColumn]),ROW("SourceTableName ","Other")),FILTER(SourceTableName, SourceTableName[CategoryColumn]<>""))

* Filter is needed when there’s bad data.

Step 2

Create Measure – TopX &Rank

TopX=n

* n is the number of the top category that you would like to display

Rank =

IF(SELECTEDVALUE(TableName[ColumnName])="Other",[TopX]+1,RANKX(ALL(TableName[ColumnName]),[Count Client]))

Step 3

Calculate the Top Category

Top = if([Rank] <= [TopX], [Count Client],

IF(SELECTEDVALUE(TableName[ColumnName])="Other",SUMX(FILTER(all(TableName),[Rank]>[TopX]),[Count Client])))

[DataInsights]

@François,

Here's an article from the masters:

https://www.sqlbi.com/articles/showing-the-top-5-products-and-others-row/