RANKX function not working in multiple hierarchy fields

[lravikiran]

  Hi ,

              I have an issue relating to RANKX function. I have a situation where I need to show the TOP Regions basing sales giving the user the option to select the TOP 1,2,3 Regions using a slicer.

To achieve this I have created a Table named 'RANK Table' which has the below fields

And in the main sheet I created a measure named 'Region Rank' which calculates the ranks of the regions basing the sales in desc order.

Region Rank = RANKX(ALL(Orders[Region]),CALCULATE(SUM(Orders[Sales])),,DESC,Dense)

Later created another measure 'Top Selector'  which relates the selection done by a user on the Rank field ( RANK Table) which limits the ranks to be equal or less than the selection done .

Eg: if the users selects  the TOP 2 , so only top 2 region will be shown .

TOP Selector = IF([Region Rank]<=SELECTEDVALUE('Rank Table'[Value]),"YES")

 This seems to work fine when only the field used in the columns is the Region field. But as soon as I add up country field which is its child Hierarchy ( Region > Country)  , the Rank don't work correctly for the Regions.

Can you please provide a solution or help to this issue , so even on adding the child hierarchy the Rank per region should operate the same way when the user selects the Slicer.

 PBIX

[v-yuta-msft · Accepted solution]

@lravikiran ,

Change measure to calculate column and use DAX like below:

Region Rank Column =
RANKX (
    FILTER ( Orders, Orders[Region] = Orders[Region] ),
    Orders[Sales],
    ,
    DESC,
    DENSE
)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lravikiran]

This is not what I want . What i want is my heirachy to be present but ranking should only happen for region level and not country level.

Please refer the image . 

I must get the rank for the basing region only inspite of the heirachy still exists.