Top N Filter when drilling through hierarchy

DAX DAX Calculations
2

Hi @Eric,

Thanks for providing a sample file :+1:
Can you see if this works for you.

Top5 By Country/City = 
VAR TopFiltered =
    CALCULATETABLE(
        GENERATE(
            VALUES( Location[County] ),
            TOPN(
                5,
                CALCULATETABLE( VALUES ( Location[City] ) ),
                [Total Profits]
            )
        ),
        ALLSELECTED()
    )
RETURN

IF( NOT( ISINSCOPE( Location[City] )), [Total Profits],
    CALCULATE( [Total Profits],
        KEEPFILTERS ( TopFiltered )
    )
)

image
image537×552 16.6 KB

Here’s your sample file. eDNA - Top5 Country by City.pbix (502.8 KB)
I hope this is helpful.

2 Likes