Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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 Please post the sample data (copiable format please) and expected output to suggest any solution.
Proud to be a PBI Community Champion
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)
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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |