Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Paul Stevens]

Hi, 

Just new to this forum so bear with me please. Reasonably new to PBI but have a good grasp of the basics. 

I was just wondering if anyone knows of a way to reassign a category name based on whether it sits in the top 3 of the category rank order. 

For example if I have 

Animal         Count of Animal      Rank
Dog             10                            1
Cat              8                              2
Rabbit         7                              3
Cow            4                              4
Horse          2                              5

Is there anyway to show it as ……..(aggregate everything > 3 rank  as “Other”) 

Animal         Count of Animal      Rank
Dog             10                            1
Cat              8                              2
Rabbit         7                              3
Other          6                             N/A

I need this to be dynamic so when filters are applied (ie change Category = Animal to Category = Type of Fish) the Rank is reassigned based on the count (and “Other” category assigned accordingly.) 

Any thoughts / suggestions would be greatly received. 

Thanks in Advance 

——————————
P
——————————

[Ashley Hsia]

?You would need a calculated column. 

Top3= IF(RANKX(YourTable,YourTable[Count of Animal])<=3,YourTable[Animal],”Other”)

If you are using the built-in counting function you may need a counting column before this will work. 

Best wishes,

——————————
AHsia
——————————
——————————————-

[Søren Amstrup]

Maybe you can find some inspiration here:
Top 10 / Otherhttps://community.powerbi.com/t5/Desktop/Top-10-Other/m-p/52120
https://blog.gbrueckl.at/2019/05/power-bi-dynamic-topn-others-with-drill-down/

——————————
Søren Amstrup
Consultant
30858032
——————————
——————————————-