How to Rank data based on Selected Hierarchy Level using DAX

How to Rank data based on Scope of Selected Level of Hierarchy using DAX
Scenario:

Suppose we have a Dataset with fields as Product Segment, Product Name, Order Quantity.

The Product Hierarchy has 2 Levels as [Product Segment] and [Product Name]

Now, based on the above Data, we can calculated the Rank of Order Quantity for each Level of the Product Hierarchy as follows:

Order Qnty = SUM(ds_Sample[OrderQnty])

Calculating Rank using the RANK function (Window):

RankQnty =

VAR vRankByProdSeg = RANK( DENSE,  ALLSELECTED(ds_Sample[ProdSegment]),

            ORDERBY([Order Qnty],DESC, ds_Sample[ProdSegment], ASC))

VAR vRankByProd = RANK( DENSE,  ALLSELECTED(ds_Sample[ProdName]),

            ORDERBY([Order Qnty],DESC, ds_Sample[ProdName], ASC))

VAR vScopeCheck =

    SWITCH( TRUE(),

        ISINSCOPE( ds_Sample[ProdName]),"ByProd",

        ISINSCOPE( ds_Sample[ProdSegment]),"ByProdSeg",

         "Others"

        )

VAR vResult =

    SWITCH( vScopeCheck,

        "ByProd",vRankByProd,

        "ByProdSeg",vRankByProdSeg,

         BLANK()

        )

RETURN

vResult

-----------------------------------------------------------

Calculating Rank using the RANKX function (Row Context):

RANKxQnty =

VAR vMinQnty = MINX(ALLEXCEPT(ds_Sample,ds_Sample[ProdSegment]), [Order Qnty])

VAR vRankByProdSeg =

    RANKX( ALLSELECTED(ds_Sample[ProdSegment]), [Order Qnty],,DESC,DENSE)

VAR vRankByProd =

    RANKX( ALLSELECTED(ds_Sample[ProdName]), [Order Qnty],,DESC,DENSE)

VAR vScopeCheck =

    SWITCH( TRUE(),

        ISINSCOPE( ds_Sample[ProdName]),"ByProd",

        ISINSCOPE( ds_Sample[ProdSegment]),"ByProdSeg",

         "Others"

        )

VAR vResult =

    SWITCH( vScopeCheck,

        "ByProd", vRankByProd,

        "ByProdSeg",vRankByProdSeg,        

         BLANK()

        )

RETURN

vResult

Results:

Now, we can see the results and behavior of both the Rank measures as per below: Rank at Level1: (Product Segment):

Rank at Level2: (Product Name):

Rank for both the Levels:

Notes: 

RankX function returns the rank of an expression evaluated for each row in a table. RankX can be used with TopN to return a set of items based on their ranking. 

The new RANK() function makes ranking on multiple columns much easier because it offers sorting by multiple columns as a native feature, because it belongs to the family of window functions.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------