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?

 

 

Thanks

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.

7 Replies

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