How to keep TopN fixed and unaffected by legend in a Bar Chart?

[Anonymous]

I wanted to group my Top 15 Buyers by highest DISTINCTCOUNT(id) and the rest as a group called "Others". I have spent 50+ hours in trying to get this right but unfortunately I am not able to.

I have implemented the following: 

1. Created a table which adds "Others" column 

Psuedo Buyer Table = UNION(
    DISTINCT('tbl1'[Buyer]),
    DATATABLE("Buyer",STRING,{{"Others"}})
)

2. Connected Tbl1 to Psuedo Buyer Table with M:1 Relationship

3. Wrote this query for getting the count for "Others"

TopNBuyers_15 = 
VAR TopBuyerTable = 
TOPN(15,ALLSELECTED('Psuedo Buyer Table'),[ID_count])
VAR TopBuyerID = CALCULATE([ID_count],KEEPFILTERS(TopBuyerTable))
VAR otherscount = CALCULATE([ID_count],ALLSELECTED('Psuedo Buyer Table'))-CALCULATE([ID_count],TopBuyerTable)
VAR currentBuyer = SELECTEDVALUE('Psuedo Buyer Table'[Buyer])
RETURN 
IF(currentBuyer<>"Others",TopBuyerID,otherscount)

Everything is working well in the Matrix visual but as soon as I change to Stacked Column Chart and add a legend, the Top 15 is not retained and it shows me top 15 for every value of that legend. It just messes up the whole thing. 

How do I retain my Top N? I have also tried another method Alberto Ferrari showed how to do this in a video
However, here my "Others" value is coming out to be negative mostly because of how this video only takes into account if we have to "SUM" something, but I am using "DISTINCTCOUNT".

I am very stuck in this issue and any help is highly appreciated.

 @dax 

[amitchandak]

@Anonymous , You should use all if want it to fixed

TOPN(15,ALL('Psuedo Buyer Table'),[ID_count])

VAR otherscount = CALCULATE([ID_count],ALL('Psuedo Buyer Table'))-CALCULATE([ID_count],TopBuyerTable)

You can also refer to my example

Power BI- TOPN with Others- https://youtu.be/I_TY4hVlzAE
https://medium.com/microsoft-power-bi/power-bi-topn-others-8b094203a306

[Anonymous]

Hello Again! I followed your youtube tutorial and everything is working really well and as expected 🙂 thank you so much for this. 
There is one edge case I am facing , using RANKX, we know it does not break ties. For example, in my OTHERS calculation my values for both ranks of 16 is getting added but I want to add only the first value and calculate my OTHERS accordingly. Is there a way to do it? @amitchandak 

[Anonymous]

Hi Amit, I have got my "Others" value correct by using the following measure for ID_count = SUMX(VALUES(Tbl1[Buyer]),CALCULATE(DISTINCTCOUNT(Tbl1[ID])))

but again, when I put the legend in my chart, my top N does not remain fixed and it shows me more buyers and not 15.

[Anonymous]

Hi Amit, I followed your youtube tutorial to reproduce it in my data. The sum of my "Others" is coming incorrect. Can it be because as you are using NET for ranking which is Sum(Sales[Net Sales]), my measure of which ranking is done is basically ID_count which is DISTINCTCOUNT(Tbl1[ID]), How do I correct my "Others" value? Could you please help?