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

[Sarakoth]

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 =

    SELECTEDVALUE('TopN'[TopN])

VAR TopProdTable =

    TOPN (

        TopNToDisplay,

        ALLSELECTED ( 'Trade Partner Names' ),

        [Total Weight]

    )

VAR TopProdSales =

    CALCULATE (

        [Total Weight],

        KEEPFILTERS ( TopProdTable )

    )

VAR OtherSales =

    CALCULATE (                  

        [Total Weight],

        ALLSELECTED ( 'Trade Partner Names' )

    )

        - CALCULATE (                        -- subtracting the sales of Top 3 products

            [Total Weight],

            TopProdTable

        )

VAR CurrentProd =

    SELECTEDVALUE ( 'Trade Partner Names'[TradePartner(withOthers)] )

RETURN

    IF (                                     -- Categorizing as Others and Top Products

        CurrentProd <> "Others",

        TopProdSales,

        OtherSales

    )

[Sarakoth]

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.

[lbendlin]

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.