cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Penn Regular Visitor
Regular Visitor

Ranking changes when applying filters

Hi all,

 

I have a example dataset that look like this.

Item IDItem GroupCostRevenue
ITEM001A12
ITEM002B25
ITEM001A13
ITEM003A13
ITEM002B23
ITEM004B34

 

And I created measures to turn it into the table visulisation as below.

Display Revenue = CALCULATE(SUM('Sales'[Revenue]))
Display Cost = CALCULATE(SUM('Sales'[Cost]))
Display Margin = [Display Revenue] - [Display Cost]
Item IDItem GroupCostRevenueMargin
ITEM001A253
ITEM002B484
ITEM003A132
ITEM004B341

 

Then I created a measure to do the ranking from the highest margin to the lowest.

Ranking = RANKX(ALL('Sales'[Item ID]), [Display Margin],,,Skip) 

 

It works fine (as the table below) if I don't apply any filter.

Item IDItem GroupCostRevenueMarginRanking
ITEM001A2532
ITEM002B4841
ITEM003A1323
ITEM004B3414

 

However, when I tried to add an Item Group slicer, the ranking breaks up in a strange way. It probably won't show up with the sample dataset but it has a huge impact on the real dataset.

1.jpg

 

The expected result should be something like the table below when applying Item Group A filter.

Item IDItem GroupCostRevenueMarginRanking
ITEM001A2531
ITEM003A1322

 

How can I fix this? Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Ranking changes when applying filters

@Penn your ranking expression should be as below

 

Ranking = RANKX(ALL('Sales'[Item Group],'Sales'[Item ID]), [Display Margin],,,Skip) 




Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




2 REPLIES 2
Super User
Super User

Re: Ranking changes when applying filters

@Penn your ranking expression should be as below

 

Ranking = RANKX(ALL('Sales'[Item Group],'Sales'[Item ID]), [Display Margin],,,Skip) 




Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




Penn Regular Visitor
Regular Visitor

Re: Ranking changes when applying filters

Hi there,

 

For some reason this meansure doesn't work for me, there is still a big jump in the rankings from 327 to 1127. It might because I have more columns in my table. Will look into this today. Thanks