Top10 with Count of rest of all as others

[jabirmoolur]

I’m creating a employees report and in that would like to show the top (10) nationalities and then have the rest of the nationalities show as “other”. ? I Can filter top10. But how can i summate rest of count others and show as 11th item?

[PattemManohar]

@jabirmoolur Please post the sample data (copiable format please) and expected output to suggest any solution.

[jabirmoolur]

Its simple Name-Nationality Mapping

I would like to see Top10 based on Nationality and 11th one should be Sum of rest of count whihc is not present in top10,
i.e after 10th item, i want to show rest of items as one item(Others)

Name	Nationality
Maria Anders	Germany
Ana Trujillo	Mexico
Antonio Moreno	Mexico
Thomas Hardy	UK
Christina Berglund	Sweden
Hanna Moos	Germany
Frédérique Citeaux	France
Martín Sommer	Spain
Laurence Lebihan	France
Elizabeth Lincoln	Canada
Victoria Ashworth	UK
Patricio Simpson	Argentina
Francisco Chang	Mexico
Yang Wang	Switzerland
Pedro Afonso	Brazil
Elizabeth Brown	UK
Sven Ottlieb	Germany
Janine Labrune	France
Ann Devon	UK
Roland Mendel	Austria
Aria Cruz	Brazil
Diego Roel	Spain
Martine Rancé	France
Maria Larsson	Sweden
Peter Franken	Germany
Carine Schmitt	France
Paolo Accorti	Italy
Lino Rodriguez	Portugal
Eduardo Saavedra	Spain
José Pedro Freyre	Spain
André Fonseca	Brazil
Howard Snyder	USA
Manuel Pereira	Venezuela
Mario Pontes	Brazil
Carlos Hernández	Venezuela
Yoshi Latimer	USA
Patricia McKenna	Ireland
Helen Bennett	UK
Philip Cramer	Germany
Daniel Tonini	France
Annette Roulet	France
Yoshi Tannamuri	Canada
John Steel	USA
Renate Messner	Germany
Jaime Yorres	USA
Carlos González	Venezuela
Felipe Izquierdo	Venezuela
Fran Wilson	USA
Giovanni Rovelli	Italy
Catherine Dewey	Belgium
Jean Fresnière	Canada
Alexander Feuer	Germany
Simon Crowther	UK
Yvonne Moncada	Argentina
Rene Phillips	USA
Henriette Pfalzheim	Germany
Marie Bertrand	France
Guillermo Fernández	Mexico
Georg Pipps	Austria
Isabel de Castro	Portugal
Bernardo Batista	Brazil
Lúcia Carvalho	Brazil
Horst Kloss	Germany
Sergio Gutiérrez	Argentina
Paula Wilson	USA
Maurizio Moroni	Italy
Janete Limeira	Brazil
Michael Holz	Switzerland
Alejandra Camino	Spain
Jonas Bergulfsen	Norway
Jose Pavarotti	USA
Hari Kumar	UK
Jytte Petersen	Denmark
Dominique Perrier	France
Art Braunschweiger	USA
Pascale Cartrain	Belgium
Liz Nixon	USA
Liu Wong	USA
Karin Josephs	Germany
Miguel Angel Paolino	Mexico
Anabela Domingues	Brazil
Helvetius Nagy	USA
Palle Ibsen	Denmark
Mary Saveley	France
Paul Henriot	France
Rita Müller	Germany
Pirkko Koskitalo	Finland
Paula Parente	Brazil
Karl Jablonski	USA
Matti Karttunen	Finland
Zbyszek Piestrzeniewicz	Poland

i.e below chart should show 11th item as others after canada (Which is sum of other countries whihc is not present in top 10)

[Anonymous · Accepted solution]

Hi @jabirmoolur

Apparently it is not that simple to do this rank + other. I found this interesting article on this subject:https://www.oraylis.de/blog/show-top-n-and-rest-in-power-bi

Hope this can help. I try to recreate this example myself and it works for everything except the total for "Others" (should be 5 instead of 8). I think the problem is coming from the  "Var TopNumber":

IF ( HASONEVALUE ( 'TopN Filter'&SQUARE_BRACKETS_OPEN;TopN] )

Not sure what he means by "SQUARE_BRACKETS__OPEN" there...

You can download my example here

Cheers

[jabirmoolur]

@Anonymous,

Thanks a lot for sharing, Now i am in a phase to convert it into sql direct query. Some functions like unions not supported