Fill table with TOPN but show "contains" filter first

[Anonymous]

I would like to create a table which shows my most important customers first and than ranks it based on margin.

The table must ranks from the highest to the lowest margin, However due to certain reasons some clients with lower margin must be shown first. The number of clients can very. By using the word "TOP20" in the name of these customer I can filter on "contains""TOP20"

However in the table it only shows the names, which contains TOP20 

I would like to make a table that first shows the customers which contains "TOP20" (for example 3 names) and than fill the rest of the table based on the highest margin.

It will look like this

TOP20 CustomerB -  $10000

TOP20 CustomerE -  $5000

TOP20 CustomerG -  $7000

CustomerA -              $100000

CustomerC -              $70000

CustomerD -             $50000

CustomerF -              $20000

CustomerH -             $20000

etc...

Does anyone know how to do this?

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may add calculated columns as shown below.

groupId =
IF ( SEARCH ( "TOP20", Table1[customer],, 0 ) = 0, 2, 1 )

rank =
RANKX ( Table1, Table1[groupId],, ASC, SKIP )
    + RANKX (
        FILTER ( Table1, Table1[groupId] = EARLIER ( Table1[groupId] ) ),
        Table1[margin],
        ,
        DESC,
        SKIP
    )
    - 1

[Anonymous]

Thank you @v-chuncz-msft for your reply.

I did what you said and made 2 extra colums

Now I assume that I have to sort the customer column by selecting the customer column en click "sort by Column""rank".

But sorting this column won't work when I try this. Am I doing something wrong?

[Anonymous]

Found it. I just have tot klik on the column header of rank to do this

Thank you very much for your help

[Anonymous]

I would like to create a table which shows my most important customers first and than ranks it based on margin.

The table must ranks from the highest to the lowest margin, However due to certain reasons some clients with lower margin must be shown first. The number of clients can very. By using the word "TOP20" in the name of these customer I can filter on "contains""TOP20"

However in the table it only shows the names, which contains TOP20 

I would like to make a table that first shows the customers which contains "TOP20" (for example 3 names) and than fill the rest of the table based on the highest margin.

It will look like this

TOP20 CustomerB -  $10000

TOP20 CustomerE -  $5000

TOP20 CustomerG -  $7000

CustomerA -              $100000

CustomerC -              $70000

CustomerD -             $50000

CustomerF -              $20000

CustomerH -             $20000

etc...

Does anyone know how to do this?