Top 20 ignoring rows in table visual

[Anonymous]

Dear,

I have been able to put together a table showing top 20 values per service line such as in the example below:

Service Line	Top 20 amounts
A	100.000
B	200.000
C	300.000
D	250.000
Total	850.000

Formula for measure Top 20 amounts = 

calculate([WIP+ 2019 per project],

filter(values('Rap+Transactions'[Project]),

if(rankx(all('Rap+Transactions'[Project]), [WIP+ 2019 per project],,desc) <= 20, [WIP+ 2019 per project], blank())))

So far, it is all working well as the table is correctly showing the top 20 amounts per service line. So top 20 for service Line A is shown, top 20 for service line B, etc.

However, as a next step, I would like to ignore the row context ot the service lines to get a top 20 of amounts over all service lines. So for example, if the top 20 largest amounts in my data source would all be linked to service lines B and C, I only want these amounts to be counted which would result in a table like this:

Service Line	Top 20 overall
A	0
B	250.000
C	350.000
D	0
Total	600.000

I tried this formula to obtain this but this doesn't ignore the row context of the service lines:

Top 20 overall =

calculate([WIP+ 2020 per project],

all( 'Rap+Transactions'[Service Line]),

filter(values('Rap+Transactions'[Project]),

if(rankx(all('Rap+Transactions'[Project]), [WIP+ 2020 per project],,desc) <= 20, [WIP+ 2020 per project], blank())))

Can anyone help me to change my first formula so that the top 20 would be recalculated to ignore the row context of service lines?

Thank you!

Best regards,

[v-yingjl · Accepted solution]

Hi @Anonymous  ，

The All() function in the rankx() function should be all ('Rap + Transactions') to ignore all filters in the table instead of specific columns. Please try to modify it as follows:

Top 20 overall =
CALCULATE (
    [WIP+ 2020 per project],
    ALL ( 'Rap+Transactions'[Service Line] ),
    FILTER (
        VALUES ( 'Rap+Transactions'[Project] ),
        IF (
            RANKX ( ALL ( 'Rap+Transactions' ), [WIP+ 2020 per project],, desc ) <= 20,
            [WIP+ 2020 per project],
            BLANK ()
        )
    )
)

And I have create a sample measure that you can refer:

Top20 overall =
VAR top20 =
    CALCULATE (
        [WIP+ 2019 per project],
        FILTER (
            'Table',
            IF (
                RANKX ( ALL ( 'Table' ), [WIP+ 2019 per project],, DESC ) <= 20,
                [WIP+ 2019 per project],
                BLANK ()
            )
        )
    )
RETURN
    IF ( ISBLANK ( top20 ), 0, top20 )

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @Anonymous ,

Withouth any data is difficult to give you an exact answer, however SQLBI released a video with a TOPN usage for the that can be adjusted to your calculation.

If you have any issues in adjusting this can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.