Simple TOPN

[johnmelbourne]

Confused.

Here is my simple as data set.

Table name = complaints

Columns: Tier2, Cases

I just want to be able to filter the table by top5 cases by Tier2 category.

Any solutions?

Thanks

[HotChilli · Accepted solution]

FilteredTable = TOPN(
5,
complaints,
complaints[cases],
DESC
)

[Mariusz · Accepted solution]

Hi @johnmelbourne 

You can use Rank as a measure like below.

Rank Cases by  Tier2= 
RANKX(
    CALCULATETABLE(
        VALUES( complaints[Tier2] ), 
        ALLSELECTED()
    ), 
    CALCULATE(
        SUM( complaints[Cases] )
    ),, 
    DESC
)

Or Column

Rank Cases by Tier2 = 
RANKX(
    VALUES( complaints[Tier2] ),
    CALCULATE( SUM( complaints[Cases] ) ),,, 
    Dense
)

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnmelbourne]

Hi @Mariusz 

I thought the TOPN to filter the table would be easy, but I am still struggling.

Here is my visualisation table using your rankx formula (which works great!). How would I use a TopN to reduce this table to say a top 5, or even better, a dynamic N using a variable? / slider?

Thanks

John

[Mariusz]

Hi @johnmelbourne 

Please see the below.

Top N Sales = 
VAR n = MAX( 'Top N Selection'[Select Top N] ) -- Unrelated Table with one column and values for top n selection, example (1, 5, 10, 15)
VAR tbl = TOPN( 
    n, 
    ALLSELECTED( 'Product'[Brand] ), -- replace with complaints[Tier2]
    [Sales], --replace with CALCULATE( SUM( complaints[Cases] ) )
    DESC 
)
RETURN 
CALCULATE(
    [Sales], --replace with SUM( complaints[Cases] )
    KEEPFILTERS( tbl )
)

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnmelbourne]

Thank you @Mariusz for the additional procedure. Love to learn. Your solutions were perfect. My TOPN approach kept on throwing an error saying multiple columns. Then you provioded your solution and I also read this article which explained why TOPN is useless by itself. https://www.dutchdatadude.com/power-bi-pro-tip-confusion-about-topn-versus-rankx/

Your contribution was perfect. Thanks @Mariusz 

[HotChilli · Accepted solution]

FilteredTable = TOPN(
5,
complaints,
complaints[cases],
DESC
)

[johnmelbourne]

This article helped, in that TOPN is useless by itself.

https://www.dutchdatadude.com/power-bi-pro-tip-confusion-about-topn-versus-rankx/

[johnmelbourne]

Thank you @HotChilli.