Forum Discussion

Vivek26's avatar
Vivek26
Icon for Advocate I rankAdvocate I
1 year ago
Solved

Rankx Not working as expected when data is filtered using slicers and filter pane

I am facing a challenge with Rankx implementation. When I use slicers to filter certain dimensions the rankx starts behaving incorrectly The dataset/Datamodel is very simple, We have 3 tables    ...
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Vivek26 

     

    Please check this measure. Although it is not concise enough, it seems to meet your requirements.

    rank = 
var currentDistrict = SELECTEDVALUE(Store[District])
var sumTable = SUMMARIZE(ALLSELECTED(Store),Store[Region],Store[District],"Total_Amount",[Total Sales])
var rankTable = ADDCOLUMNS(sumTable,"Rank_Value",RANK(DENSE,sumTable,ORDERBY([Total_Amount],DESC)))
return
MAXX(FILTER(rankTable,[District] = currentDistrict),[Rank_Value])

    I struggled a lot but haven't figured out a method with RANKX yet. Hope this would be helpful. 

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

Vivek26's avatar
Vivek26
Icon for Advocate I rankAdvocate I
1 year ago

powerbiexpert22 

 

If you filter a specific store in a district, we will again start getting rank 1 for everything . That is my main point of concern.

In a district we want to exclude certain stores . 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Vivek26 

 

Please check this measure. Although it is not concise enough, it seems to meet your requirements.

rank = 
var currentDistrict = SELECTEDVALUE(Store[District])
var sumTable = SUMMARIZE(ALLSELECTED(Store),Store[Region],Store[District],"Total_Amount",[Total Sales])
var rankTable = ADDCOLUMNS(sumTable,"Rank_Value",RANK(DENSE,sumTable,ORDERBY([Total_Amount],DESC)))
return
MAXX(FILTER(rankTable,[District] = currentDistrict),[Rank_Value])

I struggled a lot but haven't figured out a method with RANKX yet. Hope this would be helpful. 

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!