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


Get Data from WebGet Data from Web


Step 2


Insert the Wikipedia Link

Wikipedia LinkWikipedia Link

Step 3


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


Select the desired tableSelect the desired table

Step 4


Remove Top 1 Row to erase the repeat header



Remove Top 1 RowRemove Top 1 Row

Step 5


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


Select the columnsSelect the columns

Step 6


Change the data type to Population Column to Whole Number


Change to Whole Number Population columnChange to Whole Number Population column

Step 7 


Change the name of the Query to Countries and dependencies


Change name to QueryChange name to Query

Step 8


Ready. Close & Apply


Go to the Action


Step 1


Insert a Table Visual in the Canvas with both columns


Table VizTable Viz

 Step 2


Apply some format to population (Thousands separator)


Thousands separatorThousands separator

 Step 3


Sort the table visual by Population in Descending order


 Sort the table by populationSort the table by population

 Step 4


Create a new table with Enter Data


This will be used in a slicer to select TOP #

Create Top TableCreate Top Table

  Step 5


Go to Modeling New Table


Modeling New TableModeling 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" )

Countries with OthersCountries 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


Changes in table visualChanges in table visual

Step 9


Insert a slicer using Top Table


 Top SlicerTop 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] ) ),


Ranking the TableRanking 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)


Population Measure work with SlicerPopulation Measure work with Slicer

 Step 12


Let only in the visual the Countries and Population Measure.


Test it. 


Remove non neccesary columns and rankingRemove 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])


Ranking to SortRanking to Sort

Step 2


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


Table sortedTable sorted





Step 3


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


Change Name to measureChange Name to measure






Ready Smiley Happy

What is your favorite Power BI Feature release this month?