Sales Amount by Year for the Top N and Other

[akoschinek]

I have a report, that shows the Top n Sub-Categories and Other by Sales Amount.

For the selection of the Top n i created a table with values from 1 to 10.

Select Top n = {1,2,3,4,5,6,7,8,9,10}

I created another table with the Sub-Categories + Other as follows:

Sub-Category and Other =

    UNION(

    DISTINCT(Orders[Sub-Category]),

    DATATABLE("Sub-Category",STRING,{{"Other"}})

    )

The calculation for the Top n + Other:

Top n Sub-Category =

VAR _TopnSubCategory =

    TOPN (

        SELECTEDVALUE('Select Top n'[Show Top n]),

        ALLSELECTED( 'Sub-Category and Other'),

        [Sum of Sales]

    )

VAR _AllSales =

    CALCULATE (

        [Sum of Sales],

        ALL ( 'Sub-Category and Other' )

    )

VAR _TopSalesTotal =

    CALCULATE (

        [Sum of Sales],

        _TopnSubCategory

    )

VAR _TopSales =

    CALCULATE (

        [Sum of Sales],

        KEEPFILTERS ( _TopnSubCategory )

    )

VAR _OtherSales = _AllSales - _TopSalesTotal

VAR _TopSubCategories =

    SELECTEDVALUE ( 'Sub-Category and Other'[Sub-Category] )

VAR _Result =

    IF (

        _TopSubCategories <> "Other",

        _TopSales,

        _OtherSales

    )

RETURN

_Result

This is the result for top 4 that i get and expected:

Now i want to get the Sales value by year for the top 4 and Other. But when i add the year it changes my filter context and i get the top 4 by year:

This is the result i am trying to achieve is the yearly Sales amount by the top 4 of the total Sales amount:

[RekhaSikarwar]

hi , did you reached to the solution. Actually i am having a same requirement. If yes, please do share the solution.

[amitchandak]

@akoschinek , refer if this approach can help

Power BI- TOPN with Others- https://youtu.be/I_TY4hVlzAE

https://goodly.co.in/top-n-and-others-power-bi/

[akoschinek]

Thank you for your response.

I actually used this approach to get the Top N and Others. 
What i can't get to work, to split this by year without getting the Top N by year, but the yearly amount of the Overall Top N and Others.

[Ahmedx]

look hir

https://youtu.be/UhobJyo8ldE?t=2

[akoschinek]

Thank you, i wasn't aware the WINDOW function had been added. 
But from what i can see i can get the top N Sub-Categories by year, instead of having the overall top N and then taking these and het their value per year.