Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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)
!_Brand Group = IF([!_Brand Rank] <= 10, 'Reg Ranking for Top Each Year'[Brand], "OTHER")
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
@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