TOP N Matrix and expand all down in hierarchy

[Sayri]

Dear community,

I searched for into the forum and tried multiple solutions, but none of them is working in my case.
Unfortunately for confidentiality reasons, I'm not able to share a sample of the data
But I'm trying to do that:
in a Matrix having a Top 3 per level and keep the top 3 each time I'm expanding all down in the hierarchy
 

Thanks to the forum I've already found and tried the two following links with no success:
https://dataninjago.com/2017/12/11/dax-ranking-with-hierarchy-drill-down/
https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/
I tried different ways, whith TOPN or RANKX but each time the first level is ranking but, expanding all down to the 2 next levels there is no more Ranking.
Any tips / ideas please ?

[amitchandak]

@Sayri , refer this. At last they using rank with insinscope

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

[CNENFRNL]

Hi, @Sayri , since you're intended to extract top 3 in each level, you need to calculate ranks in each level respectively. you might want to use such a pattern,

Top3 =
VAR __rank =
    SWITCH (
        TRUE (),
        ISINSCOPE ( Category[Lvl3] ), RANKX ( ALLSELECTED ( Category[Lvl3] ), [Total Sales] ),
        ISINSCOPE ( Category[Lvl2] ), RANKX ( ALLSELECTED ( Category[Lvl2] ), [Total Sales] ),
        ISINSCOPE ( Category[Lvl1] ), RANKX ( ALLSELECTED ( Category[Lvl1] ), [Total Sales] )
    )
RETURN
    IF ( __rank <= 3, [Total Sales] )

[Sayri]

Thanks @CNENFRNL 
and Happy New Year !!
actually it works perfectly for the 2 first Categories, 

for the 3d I had to add some conditions like below, and it's nearly working except...

Top3 = 
VAR __rank =
    SWITCH (
        TRUE (),
        ISINSCOPE ('Case'[Lvl1]) && ISINSCOPE('Case'[Lvl12] ) && ISINSCOPE ('Case'[Lvl13] ), RANKX ( ALLSELECTED ( 'Case'[Lvl13] ), [Total Sales] ),
        ISINSCOPE ('Case'[Lvl1]) && ISINSCOPE('Case'[Lvl12]) && NOT ISINSCOPE ('Case'[Lvl13]), RANKX ( ALLSELECTED ( 'Case'[Lvl12] ), [Total Sales] ),
        ISINSCOPE ('Case'[Lvl1]) && NOT ISINSCOPE('Case'[Lvl12]) && NOT ISINSCOPE ('Case'[Lvl13]), RANKX ( ALLSELECTED ( 'Case'[Lvl13] ), [Total Sales] )
        
    )
RETURN
    IF ( __rank <= 3, [Total Sales])

Even empty, it's still displaying the previous categ (see the screenshot below)
I tried to add 'BLANK ()' to the IF condition,
'RETURN
IF ( __rank <= 3, [Total Sales], BLANK() )'
but it has no effect on what I would like hide
Any tips, idea please ? 

'RETURN
IF ( __rank <= 3, [Total Sales], BLANK() )'

[amitchandak]

@Sayri , Try to create a rank meausre like this and filter Rank <=3

rankx(filter(allselected(Table[category], Table[SubCateg]), Table[category] =max(Table[category])), [sales value],,desc,dense)

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[Sayri]

Thank you @amitchandak 
Actually what you propose is working fine for the second level of the hierarchy, but not for the Top Level 😕 (all Rank remain 1 there)
And there is 3 Levels of hierarchy in the matrix
May you have any other idea ?