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 
 
Dimension table - Store( Storeid, District,Region) 
Fact Table - Sales (SalesID,Storeid,SalesDate,Amount)
Calendar( For time Series analysis) -(Date,day,MonthName,Month,Quarter)
 
Store to Sales - One to many relationship on the basis of Storeid
Calendar to Sales - One to many relationship on the basis of Date 
 
This is how my Store table looks like 
StoreId District Region
Store1 Mumbai North
Store2 Nagpur East
Store3 Kochi North
Store4 Hyderabad West
Store5 Delhi North
Store6 Kanpur East
 
This is how my sales data looks like , They have a one to many relationship on the basis of StoreId.
 
ID StoreId SalesDate Amount
1 Store87 15-06-2020 41
2 Store99 10-07-2021 44
3 Store70 29-06-2018 39
4 Store31 05-04-2024 32
5 Store56 19-01-2023 12
6 Store25 11-02-2021 79
 
 
Now I want to rank on the basis of District and sales   and I am using Region District StoreId in my slicer .
The output should look like this 
 
District|Total Sales|RankBasedonSales
Delhi|10000|1
Kanpur|9000|2
Nagpur|8000|3
 
Here Total Sales = SUM(Sales[Amount])
 
when I write the below dax to calculate the rank, It works perfectly if I don't filter anything on my slicer.
Once I start filtering using slicer the dax breaks  
 
RankBasedOnSales-Dimension = 
RANKX(
    ALLSELECTED(Store[District]),
    [Total Sales]
)
 
To Test another approach I added a calculated column in District in my fact table using the related function 
and I called it calculated District Then I modified the dax as below 
 
rankbasedonsales = 
RANKX(
ALLSELECTED(Sales[Calculated District]),
[Total Sales]
) 
after implementing this the dax works perfectly( for this I will use calculated district in my visual along with sales and rank)
I don't want to add a column for obvious reasons, I am sure we can do it without using the calculated column .
Can you please help me here 

 

  • 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!

9 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    1 year ago

    Vivek26 What do you mean by "DAX breaks"? You get an error, you get incorrect results, ... ? You might try RANK instead of RANKX

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

    Greg_Deckler 

    Hi Greg , Thanks for replying.

    The dax doesn't break but the ranking order gets messed up 

    For example post filtering , Kanpur may get the 3rd Rank while it should be 2nd 

     

    District|Total Sales|RankBasedonSales
    Delhi|10000|1
    Kanpur|9000|3
    Nagpur|8000|2
  • Vivek26's avatar
    Vivek26
    Icon for Advocate I rankAdvocate I
    1 year ago

    powerbiexpert22 . Thanks for replying.

    I used your pbix but instead of having 3 different slicers . I used a single one and I started getting the same issue.

    My Apoligies I should have clarified my usage of slicer

     

     

    • powerbiexpert22's avatar
      powerbiexpert22
      Icon for Impactful Individual rankImpactful Individual
      1 year ago

      Hi Vivek26 ,

      try to use below 

      rank =
      RANKX(ALLSELECTED(store[region],store[district],store[storeid]),[Total Sales],,DESC,Dense)
       

       

  • 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!

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

    Anonymous . Thanks a lot for the reply. The solution worked.

    Please do let me know if you are able to solve it with Rankx as well.

    I also Invested a lot of time with Rankx but I was not able to find a good solution.