Can we use RankX with drilleddown option ?

[DeepDive]

I have Customer, Country, Product, and Sales Value.. I want to use Rankx on customer but when i drilled it down further on country level... it will show rank of customer within in country(single) and same for Product like rank of Customers in one country for single producte... I want to use matrix here (and also can we use group by to get top 5 customers and rest in others ).. Experts pls help

[v-diye-msft]

Hi 

We can barely replicate your issue, plz kindly share more details, thanks.

[DeepDive]

Dear all,

am using below DAX, did some R&D also but unable to get the desired reult. 

Rank_Customer =

IF(HASONEVALUE(Sales_Data[Customer]),

CALCULATE(RANKX(ALL(Sales_Data[Customer]), [Total_Value],,DESC,Dense), ALLSELECTED(Sales_Data[Country]), ALLSELECTED(Sales_Data[Product])),

BLANK()

)

[Cmcmahan · Accepted solution]

It took me a bit, but I was able to find this blog post that explained exactly how to do it.  I've updated their expression to use ISINSCOPE, which works better for matrices.

Rank = 
IF( ISINSCOPE(Sales_Data[Product]), RANKX(ALL(Sales_Data[Product]), CALCULATE(SUM(Sales_Data[Sales]))),
    IF( ISINSCOPE(Sales_Data[Country]), RANKX(ALL(Sales_Data[Country]), CALCULATE(SUM(Sales_Data[Sales]))), 
        IF(ISINSCOPE(Sales_Data[Customer]), RANKX(ALL(Sales_Data[Customer]), CALCULATE(SUM(Sales_Data[Sales]))))
    )
)

This will return the rank for whatever scope you're currently in.  Using this, I was able to create this visual, where the rank responds to the current level of drilldown, and will still rank against values that are filtered out of the visual:

[DeepDive]

[Cmcmahan]

Can you share the DAX expression you're using RANKX in?  You likely just need to add in an ALL statement to your syntax, but without knowing what it looks like, I can't help much.