Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

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

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 

4 REPLIES 4
amitchandak
Super User
Super User

@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
Not applicable

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
Not applicable

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
Not applicable

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?

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.