cancel
Showing results for 
Search instead for 
Did you mean: 

Show Tops & Others in a Table Visual

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

 

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 =
UNION (
    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)

 

14.pngRelationship

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 = 
RANKX (
    ALL ( 'Countries Table'[Country (or dependent territory)]),
    CALCULATE ( SUM ( 'Countries & Dependencies'[Population] ) ),
    ,
    DESC
)

 

17.pngRanking 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)

 

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

 

 

22.png

 

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

 

Result

 

24.pngFinished

 

Ready Smiley Happy

Comments

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