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      ...
  • OwenAuger's avatar
    10 years ago

    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.