cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Susanta91
Frequent Visitor

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

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:

Parent_Child hierarchy.png
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.
)Screenshot 2022-09-21 120328.png

 

 

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

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

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.

RicoZhou_0-1663835514954.png

 

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.

View solution in original post

2 REPLIES 2
RicoZhou
Community Support
Community Support

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.

RicoZhou_0-1663835514954.png

 

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.

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.3rd level Hie.png

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors