Forum Discussion

johnmelbourne's avatar
johnmelbourne
Helper V
7 years ago
Solved

Simple TOPN

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?   ...
Need Help
  • HotChilli's avatar
    HotChilli
    7 years ago
    FilteredTable = TOPN(
5,
complaints,
complaints[cases],
DESC
)
  • Mariusz's avatar
    Mariusz
    7 years ago

    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's avatar
johnmelbourne
Helper V
7 years ago

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's avatar
Mariusz
Community Champion
7 years ago

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.