Show Tops & Others in a Table Visual

by Super User on ‎01-28-2019 07:18 PM

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

by Clydemaxwell Frequent Visitor
on ‎02-04-2019 03:08 PM

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