Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Vvelarde

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

 

Get Data from WebGet Data from Web

 

Step 2

 

Insert the Wikipedia Link

Wikipedia LinkWikipedia Link

Step 3

 

Select the desired table "Sovereign states and dependencies..." and Click Edit

 

Select the desired tableSelect the desired table

Step 4

 

Remove Top 1 Row to erase the repeat header

 

 

Remove Top 1 RowRemove Top 1 Row

Step 5

 

Select Country and Population columns after Right Click --Choose Remove Other Columns

 

Select the columnsSelect the columns

Step 6

 

Change the data type to Population Column to Whole Number

 

Change to Whole Number Population columnChange to Whole Number Population column

Step 7 

 

Change the name of the Query to Countries and dependencies

 

Change name to QueryChange 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 VizTable Viz

 Step 2

 

Apply some format to population (Thousands separator)

 

Thousands separatorThousands separator

 Step 3

 

Sort the table visual by Population in Descending order

 

 Sort the table by populationSort 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 TableCreate Top Table

  Step 5

 

Go to Modeling New Table

 

Modeling New TableModeling 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 OthersCountries with Others

Step 7

 

Relate both tables (One to Many)

 

RelationshipRelationship

Step 8

 

Modify the table visual to use the Country (or dependant territory) from Countries Table

 

Changes in table visualChanges in table visual

Step 9

 

Insert a slicer using Top Table

 

 Top SlicerTop 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 TableRanking 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 SlicerPopulation Measure work with Slicer

 Step 12

 

Let only in the visual the Countries and Population Measure.

 

Test it. 

 

Remove non neccesary columns and rankingRemove 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 SortRanking to Sort

Step 2

 

Sort the table visual by "Ranking to sort" measure in ascending order.

 

Table sortedTable sorted

 

 

22.png

 

Step 3

 

Change the name in Values of "Ranking to Sort" to R. After that hide reducing his width (manually).

 

Change Name to measureChange Name to measure

 

Result

 

FinishedFinished

 

Ready Smiley Happy

Comments
Anonymous

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