Showing results for 
Search instead for 
Did you mean: 

Show Tops & Others in a Table Visual

The Data


Sample Data from Wikipedia


Data Preparation


Step 1:


Select Get Data From Web


1.pngGet Data from Web


Step 2


Insert the Wikipedia Link

2.pngWikipedia Link

Step 3


Select the desired table "Sovereign states and dependencies..." and Click Edit


3.pngSelect the desired table

Step 4


Remove Top 1 Row to erase the repeat header



4.pngRemove Top 1 Row

Step 5


Select Country and Population columns after Right Click --Choose Remove Other Columns


5.pngSelect the columns

Step 6


Change the data type to Population Column to Whole Number


6.pngChange to Whole Number Population column

Step 7 


Change the name of the Query to Countries and dependencies


7.pngChange name to Query

Step 8


Ready. Close & Apply


Go to the Action


Step 1


Insert a Table Visual in the Canvas with both columns


8.pngTable Viz

 Step 2


Apply some format to population (Thousands separator)


9.pngThousands separator

 Step 3


Sort the table visual by Population in Descending order


 10.pngSort the table by population

 Step 4


Create a new table with Enter Data


This will be used in a slicer to select TOP #

11.pngCreate Top Table

  Step 5


Go to Modeling New Table


12.pngModeling New Table

Step 6


Create a table with Countries and aditional row to Others


Countries =
    VALUES ( 'Countries and dependencies'[Country (or dependent territory)] ),
    ROW ( "Country (or dependent territory)", "Others" )

13.pngCountries with Others

Step 7


Relate both tables (One to Many)



Step 8


Modify the table visual to use the Country (or dependant territory) from Countries Table


16.pngChanges in table visual

Step 9


Insert a slicer using Top Table


 15.pngTop Slicer


Step 10


Create a measure to Rank to countries by population


Ranking = 
    ALL ( 'Countries Table'[Country (or dependent territory)]),
    CALCULATE ( SUM ( 'Countries & Dependencies'[Population] ) ),


17.pngRanking the Table

 Step 11


Create a measure to work with TOP slicer


Population_M = 
VAR _countryselected=SELECTEDVALUE('Countries Table'[Country (or dependent territory)])
VAR _population=Sum('Countries & Dependencies'[Population])
VAR _populationOthers=CALCULATE(SUM('Countries & Dependencies'[Population]),FILTER(ALL('Countries Table'[Country (or dependent territory)]),[Ranking]>_LimiteTop))
IF(_countryselected<>"Others", IF([Ranking]<=_LimiteTop,_population),_populationOthers)


18.pngPopulation Measure work with Slicer

 Step 12


Let only in the visual the Countries and Population Measure.


Test it. 


19.pngRemove non neccesary columns and ranking

Extra: The order Issue


Ok, Working fine but the order, Others should be at last of the countries in the visual


***The next steps is working in a table visual in a chart by the moment , No.


Step 1


Create a measure to Ranking the countries including others


Ranking to Sort = IF([Population_M]<>BLANK(),[Ranking])


20.pngRanking to Sort

Step 2


Sort the table visual by "Ranking to sort" measure in ascending order.


21.pngTable sorted





Step 3


Change the name in Values of "Ranking to Sort" to R. After that hide reducing his width (manually).


23.pngChange Name to measure






Ready Smiley Happy


Awesome infro, thanks. Really helped me out.Smiley Very Happy