The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
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.