Scenario:
Suppose we have a Dataset with fields as Product Segment, Product Name, Order Quantity.
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])
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 for both the Levels:
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
——————————————————————————————————–
Be the first to comment