RANKX for Values from several years

[mbylicki]

I have a fact table ('Reg Ranking for Top Each Year') which contains car sales in each year looks like

Brand | Year | Value

Audi   | 2019 | 13777

BMW  | 2019 | 20699

and additional linked table with calendar.

What i want to achieve is to show TOP selling Brands for selected Years and grouped rest as Other.

I created a Measure -> 

!_registrations = sum('Reg Ranking for Top Each Year'[Value])

then another one for ranking -> 

!_Brand Rank = RANKX(ALLSELECTED('Reg Ranking for Top Each Year'[Brand]), CALCULATE([!_registrations], ALLEXCEPT('Reg Ranking for Top Each Year', 'Reg Ranking for Top Each Year'[Brand]), 'Reg Ranking for Top Each Year'[Year] IN VALUES('Calendar'[Year] )),,DESC,Skip)

and finally the calculated column -> 

!_Brand Group = IF([!_Brand Rank] <= 10, 'Reg Ranking for Top Each Year'[Brand], "OTHER")

Everything looks fine when I select only one year - I get TOP 10 Brands and all others grouped as "OTHER" but when I select more years I start to have more then 10 brands -> Brand which appears at least once in one of selected year come up on the list ->

Renault should not appear because in selected years sold 21323 cars and Ford sold 22096 and should stay on the list (at 10th place in my case)

TOP10 for 2023 | TOP10 for 2022 | TOP10 for 2022-2023 (is now) | Should be
Audi        | Audi         | Audi         | Audi
BMW       | BMW        | BMW       | BMW
Dacia       | Dacia        | Dacia       | Dacia
                | Ford         | Ford         | Ford
Hyundai   | Hyundai   | Hyundai   | Hyundai
KIA           | KIA           | KIA           | KIA
Mercedes | Mercedes | Mercedes | Mercedes
Renault     |                 | Renault     |
Skoda       | Skoda      | Skoda       | Skoda
Toyota      | Toyota      | Toyota      | Toyota
VW           | VW           | VW           | VW

I suspect that my error is in "IN VALUES" statements, but I'm not sure - could somebody help me 🙂

Thanks

Maciej  

[amitchandak]

@mbylicki , check if this solution can help when you add year also to visual

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