Skip to main content
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.

Frequent Visitor

Select Top N Categories for all selected years, but the display values for all years?

Hi Everyone,


I want to do some charting utilising a dynamic Top N approach. After much work I have it working in general but I am hoping I can tweak it further.


Here is some sample data/visuals:





The left table correctly shows the Top 6 Trade Partners and rolls the rest up into an "Others" category.


However when I want to chart or display these Trade Partners, by adding in the Year context, the measure now shows the Top 6 per year. Meaning the left table now has NO values for "Viet Nam" and "Thailand" in 2020 despite them being in the Top 6 overall for the 3 selected Years. You can see that "Japan" and "Malaysia" are NOT in the top 6 overall, but are in the Top 6 for 2020 only. 


Is there a way to filter second yearly table by the Trade Partners displayed in the first table? I would like to plot or show values for all years for "Viet Nam" and "Thailand" as currently their 2020 values are being rolled up into "Others" which is confusing. It looks even worse when you plot on a chart.


Also this is the tutorial I was roughly following.


Display Top N items and Others in Power BI - Goodly


You can see that they use the yearly slicer, but only ever select a single year. So the chart is always correct. 


My current measure is:


Top N Sum =
VAR TopNToDisplay =
VAR TopProdTable =
    TOPN (
        ALLSELECTED ( 'Trade Partner Names' ),
        [Total Weight]
VAR TopProdSales =
        [Total Weight],
        KEEPFILTERS ( TopProdTable )
VAR OtherSales =
    CALCULATE (                  
        [Total Weight],
        ALLSELECTED ( 'Trade Partner Names' )
        - CALCULATE (                        -- subtracting the sales of Top 3 products
            [Total Weight],
VAR CurrentProd =
    SELECTEDVALUE ( 'Trade Partner Names'[TradePartner(withOthers)] )
    IF (                                     -- Categorizing as Others and Top Products
        CurrentProd <> "Others",







Frequent Visitor

Thanks for taking the time to respond. I appreciate it.


I know you could do something like this in SQL with a sub select... trying to think how to do it in Power BI. I think it should be possible to get say the top 5 countires for overall sales (for the selected years), then show me all the individual sales for those countries for all years. I will keep thinking.

Super User
Super User

Your requirements are conflicting with each other. You need to decide if you only want to go by the combined years filter context or if you want to consider the in-year ranking. You can't have both.

Helpful resources

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


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.