Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Step 2
Insert the Wikipedia Link
Step 3
Select the desired table "Sovereign states and dependencies..." and Click Edit
Step 4
Remove Top 1 Row to erase the repeat header
Step 5
Select Country and Population columns after Right Click --Choose Remove Other Columns
Step 6
Change the data type to Population Column to Whole Number
Step 7
Change the name of the Query to Countries and dependencies
Step 8
Ready. Close & Apply
Go to the Action
Step 1
Insert a Table Visual in the Canvas with both columns
Step 2
Apply some format to population (Thousands separator)
Step 3
Sort the table visual by Population in Descending order
Step 4
Create a new table with Enter Data
This will be used in a slicer to select TOP #
Step 5
Go to 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" ) )
Step 7
Relate both tables (One to Many)
Step 8
Modify the table visual to use the Country (or dependant territory) from Countries Table
Step 9
Insert a slicer using Top Table
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 )
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)
Step 12
Let only in the visual the Countries and Population Measure.
Test it.
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])
Step 2
Sort the table visual by "Ranking to sort" measure in ascending order.
Step 3
Change the name in Values of "Ranking to Sort" to R. After that hide reducing his width (manually).
Result
Ready
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.