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 :smileyhappy:
Community Champion