cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Fill table with TOPN but show "contains" filter first

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Fill table with TOPN but show "contains" filter first

@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
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Table filter TopN but show "contains" filter first

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?

Community Support Team
Community Support Team

Re: Fill table with TOPN but show "contains" filter first

@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
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

Re: Fill table with TOPN but show "contains" filter first

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
Not applicable

Re: Fill table with TOPN but show "contains" filter first

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

 

Thank you very much for your help

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors