Top N works 2nd & 3rd Level drill down(Hierarchy) but no luck for Top Level/ Parent Level (Country)

[Susanta91]

Failed to use TOP N slicer on Parent level hierarchy. I have 3 level hierarchy Country > State > City. Wrote a Dax that returns TOP selected value State & City level not the Country Level.

I want TOP N slicer should work with all of my 3 level hierarchy. Let's consider TOPN filter = 2, will return TOP 2 on Country as well as State & city level.

Sharing the dataset & measure I have written to get ease of scenario:


My approach was: 

Top5 By Country/City Prac = Demo PBIX File  

VAR TopFilteredCity =

    CALCULATETABLE(

        GENERATE(

            VALUES( Location[County] ),

            TOPN(

                [TOP Selection Value],

                CALCULATETABLE( VALUES ( Location[City] ) ),

                [Total Profits]

            )

        ),

        ALLSELECTED()

    )

VAR TopFilteredState =

    CALCULATETABLE(

        GENERATE(

            VALUES( Location[City] ),

            TOPN(

                [TOP Selection Value],

                CALCULATETABLE( VALUES ( Location[State] ) ),

                [Total Profits]

            )

        ),

        ALLSELECTED()

    )

VAR TopFilteredCountry =

    CALCULATETABLE(

            VALUES( Location[County] ),

            TOPN(

                [TOP Selection Value],

                ALL ( Location[County] ),

                [Total Profits]

            )

    )

RETURN

SWITCH (

    TRUE (),

    ISINSCOPE( Location[City]), CALCULATE( [Total Profits], KEEPFILTERS ( TopFilteredCity )) ,

    ISINSCOPE( Location[State]), CALCULATE( [Total Profits], KEEPFILTERS ( TopFilteredState )) ,

    ISINSCOPE( Location[County]), CALCULATE( [Total Profits], KEEPFILTERS ( TopFilteredCountry)) ,

    [Total Profits]

Changed something in existing DAX, happy to get information If any changes required to be sort entire DAX.

)

 Would request some assistance on this topic. Thanks in advance. 

[v-rzhou-msft · Accepted solution]

Hi @Susanta91 ,

Here I suggest you to create a new measure based on [Top5 By Country/City] measure.

Top N with correct total = 
IF(
    ISBLANK([Top5 By Country/City]),
    BLANK(),
    SUMX(SUMMARIZE(Location,Location[County],Location[City],Location[State]),[Top5 By Country/City])
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-rzhou-msft · Accepted solution]

Hi @Susanta91 ,

Here I suggest you to create a new measure based on [Top5 By Country/City] measure.

Top N with correct total = 
IF(
    ISBLANK([Top5 By Country/City]),
    BLANK(),
    SUMX(SUMMARIZE(Location,Location[County],Location[City],Location[State]),[Top5 By Country/City])
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Susanta91 · Accepted solution]

I would appreciate saying that my purpose has been solved now. All three hierarchies has been given output based on TOPN slicer, just works likely as per my requirement. Thanks Again Rico. PFB the attachment.