RANKX drill down matrix not working

[Anonymous]

Hi, i am trying to create a rankx that recalculates when you drill down in a matrix table i have tried about 100 different ways currently my table looks like this;

the top level works in regards to ranking but when i drill down it just shows ones the dax measure i am using is the following;

Endleaf Rank = SWITCH ( TRUE (), ISINSCOPE ( Sales[Sales Executive] ), RANKX ( ALLSELECTED ( Sales[Sales Executive] ), [Total Points], , DESC, DENSE ), ISINSCOPE ( Sales[Month] ), RANKX ( ALLSELECTED ( Sales[Month] ), [Total Points], , DESC, DENSE ), ISINSCOPE ( Sales[Store] ), RANKX ( ALLSELECTED ( Sales[Store] ), [Total Points], , DESC, DENSE ), ISINSCOPE (Sales[Product] ), RANKX ( ALLSELECTED ( Sales[Product] ), [Total Points], , DESC, DENSE ) )

[Fowmy · Accepted solution]

@Anonymous 

Your measure is correct but the order in which you ordered the columns needs to change from lower to upper, try this measure, please.

Endleaf Rank =
SWITCH (
    TRUE (),
    ISINSCOPE ( Sales[Product] ), RANKX ( ALLSELECTED ( Sales[Product] ), [Total Points],, DESC, DENSE ),
    ISINSCOPE ( Sales[Store] ), RANKX ( ALLSELECTED ( Sales[Store] ), [Total Points],, DESC, DENSE ),
    ISINSCOPE ( Sales[Month] ), RANKX ( ALLSELECTED ( Sales[Month] ), [Total Points],, DESC, DENSE ),
    ISINSCOPE ( Sales[Sales Executive] ), RANKX ( ALLSELECTED ( Sales[Sales Executive] ), [Total Points],, DESC, DENSE )
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[Anonymous]

@Fowmy thank you so much, have been struggling with this for so long

[amitchandak]

@Anonymous , once you drill you need categorical rank or subcategory rank, refer those in the bold link

so refer

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/367415