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

[Vivek26]

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 

[Vivek26]

@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.

[Vivek26]

@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 · Accepted solution]

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!

[powerbiexpert22]

Hi @Vivek26 ,

can you let me know in which scenario in my pbix file is giving all ones?

[Vivek26]

@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]

Hi @Vivek26 ,

try to use below 

rank =

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

[powerbiexpert22]

Hi @Vivek26,

please see below pbix for your reference , the rankx function is showing correct results

you will have to create measure first for sales amount and then use it inside rank function ( do not use sum(col) directly in your rank measure

https://drive.google.com/file/d/1TmjidSpb2KktCGYUkfRDQlnc_nAWsL3L/view?usp=drive_link

[Vivek26]

@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

[Greg_Deckler]

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