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
