Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

Creating TopN with two levels

I have a table with Continent, Countries and product. Sample table below

 

Continent   Country   Product

Asia             India       A

Asia             China      B

Asia             India       C

Europe        Germany D

Europe        France     E

Europe        Denmark F

 

I would like to get the Top 10 countries per Continent in a Table. I have searched online, but most of the TopN and Rank formula are for just 1 level of TopN. Any help would be really appreciated.

  • Hi there,

     

    Here is how I would do it - I assume you're ranking using a measure.

    Change [Your Measure] to the measure used for ranking and YourTable to the appropriate table name.

     

     

    Your Measure for Top 10 Countries Per Continent = 
    CALCULATE (
        [Your Measure],
        KEEPFILTERS (
            GENERATE (
                VALUES ( YourTable[Continent] ),
                TOPN ( 10, ALL ( YourTable[Country] ), [Your Measure] )
            )
        )
    )

     

    You could also do it this way if your measure can be summed by Continent:

     

    Your Measure for Top 10 Countries Per Continent v2 =
    SUMX (
        VALUES ( YourTable[Continent] ),
        CALCULATE (
            [Your Measure],
            TOPN ( 10, ALL ( YourTable[Country] ), [Your Measure] ),
            VALUES ( YourTable[Country] )
        )
    )

     

    If you just want to display the Countries but not the measure itself, you can use a Visual Level Filter on this measure to nonblank.

2 Replies

  • Hi there,

     

    Here is how I would do it - I assume you're ranking using a measure.

    Change [Your Measure] to the measure used for ranking and YourTable to the appropriate table name.

     

     

    Your Measure for Top 10 Countries Per Continent = 
    CALCULATE (
        [Your Measure],
        KEEPFILTERS (
            GENERATE (
                VALUES ( YourTable[Continent] ),
                TOPN ( 10, ALL ( YourTable[Country] ), [Your Measure] )
            )
        )
    )

     

    You could also do it this way if your measure can be summed by Continent:

     

    Your Measure for Top 10 Countries Per Continent v2 =
    SUMX (
        VALUES ( YourTable[Continent] ),
        CALCULATE (
            [Your Measure],
            TOPN ( 10, ALL ( YourTable[Country] ), [Your Measure] ),
            VALUES ( YourTable[Country] )
        )
    )

     

    If you just want to display the Countries but not the measure itself, you can use a Visual Level Filter on this measure to nonblank.

    • Anonymous's avatar
      Anonymous
      Not applicable

      This is wonderful! Works perfectly! Thanks :manhappy: