Sample Data from Wikipedia
Select Get Data From Web
Insert the Wikipedia Link
Select the desired table "Sovereign states and dependencies..." and Click Edit
Remove Top 1 Row to erase the repeat header
Select Country and Population columns after Right Click --Choose Remove Other Columns
Change the data type to Population Column to Whole Number
Change the name of the Query to Countries and dependencies
Ready. Close & Apply
Go to the Action
Insert a Table Visual in the Canvas with both columns
Apply some format to population (Thousands separator)
Sort the table visual by Population in Descending order
Create a new table with Enter Data
This will be used in a slicer to select TOP #
Go to Modeling New Table
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" ) )
Relate both tables (One to Many)
Modify the table visual to use the Country (or dependant territory) from Countries Table
Insert a slicer using Top Table
Create a measure to Rank to countries by population
Ranking = RANKX ( ALL ( 'Countries Table'[Country (or dependent territory)]), CALCULATE ( SUM ( 'Countries & Dependencies'[Population] ) ), , DESC )
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)
Let only in the visual the Countries and Population Measure.
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.
Create a measure to Ranking the countries including others
Ranking to Sort = IF([Population_M]<>BLANK(),[Ranking])
Sort the table visual by "Ranking to sort" measure in ascending order.
Change the name in Values of "Ranking to Sort" to R. After that hide reducing his width (manually).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.