Forum Discussion

jayt93's avatar
jayt93
Frequent Visitor
2 years ago
Solved

RankX Duplicates when multiple slicer filter selected

Hi everyone,

 

I've been trying to figure this one out for a while but couldn't come up with a solution.

I have a slicer for a Category and Year among a few others.

 

I have a table visual that shows the following:

Item IDItem DescriptionPriceRankPrior Yr PricePrior Yr Rank
001ABC501481
002BCD452403
003CDE403452
004EFG354275
005FGH305304

 

This works perfectly fine when one of the the categories is selected OR if all the categories are selected but not when a mix of them are selected (For example, only Category A & Category C). In those cases duplicates ranks would show up and it seems like it's evaluating the rank for each item based on the Category individually rather than together.

 

This is how I was calculating the prior year rank:

Prior Yr Rank = 

var py_table = filter(ALLEXCEPT(Table, Table[Categories]),Table[Year]=MAX(Table[Year])-1)
var py_filter = filter(ALLEXCEPT(Table, Table[Categories], Table[Item ID]),Table[Year]=MAX(Table[Year])-1)
return
rankx(py_table, CALCULATE(SUM(Table[Price],py_filter))
 
Any help would be greatly appreciated.
Need Help

9 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Do you want to keep the rank even if you change the filter context?  Did you use calculated columns or measures?

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Share data in a format that can be pasted in an MS Excel file and show the expected result clearly.  Also, clarify the result if there are ties - show that in the expected result.

  • jayt93's avatar
    jayt93
    Frequent Visitor
    2 years ago

    Ashish_Mathur lbendlin 

    My apologies I should have attached a sample data set.

    In the sample file this is what I'm trying to achieve:

    Any help would be greatly appreciated, thanks!