Creating TopN with two levels

[Anonymous]

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.

[OwenAuger · Accepted solution]

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]

This is wonderful! Works perfectly! Thanks