Show Tops & Others in a Table Visual

[Vvelarde]

The Data

Sample Data from Wikipedia

https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population

Data Preparation

Step 1:

Select Get Data From Web

Get Data from Web

Step 2

Insert the Wikipedia Link

Wikipedia Link

Step 3

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

Select the desired table

Step 4

Remove Top 1 Row to erase the repeat header

Remove Top 1 Row

Step 5

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

Select the columns

Step 6

Change the data type to Population Column to Whole Number

Change to Whole Number Population column

Step 7 

Change the name of the Query to Countries and dependencies

Change 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 Viz

 Step 2

Apply some format to population (Thousands separator)

Thousands separator

 Step 3

Sort the table visual by Population in Descending order

 Sort 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 Table

  Step 5

Go to Modeling New Table

Modeling New Table

Step 6

Create a table with Countries and aditional row to Others

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

Countries with Others

Step 7

Relate both tables (One to Many)

Relationship

Step 8

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

Changes in table visual

Step 9

Insert a slicer using Top Table

 Top Slicer

Step 10

Create a measure to Rank to countries by population

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

Ranking the Table

 Step 11

Create a measure to work with TOP slicer

Population_M = 
VAR _LimiteTop=SELECTEDVALUE(Tops[TOP])
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))
RETURN
IF(_countryselected<>"Others", IF([Ranking]<=_LimiteTop,_population),_populationOthers)

Population Measure work with Slicer

 Step 12

Let only in the visual the Countries and Population Measure.

Test it. 

Remove 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 Sort

Step 2

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

Table sorted

Step 3

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

Change Name to measure

Result

Finished

Ready