RankX Duplicates when multiple slicer filter selected

[jayt93]

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 ID	Item Description	Price	Rank	Prior Yr Price	Prior Yr Rank
001	ABC	50	1	48	1
002	BCD	45	2	40	3
003	CDE	40	3	45	2
004	EFG	35	4	27	5
005	FGH	30	5	30	4

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.

[jayt93]

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

[Ashish_Mathur]

Hi,

When i click on Download, I am take to a sign-in or sign-up page.

[lbendlin]

@Ashish_Mathur see attached

[Ashish_Mathur · Accepted solution]

Thank you @lbendlin.

@jayt93 - please find attached the PBI file.  Had to make a lot of changes in there.  Please review the file carefully.

[jayt93]

Amazing, thank you Ashish! I will try applying this to my actual data set. I didn't even think of creating another date table for this. Just out of curiosity is it not possible to achieve the same result without creating a date table?

[Ashish_Mathur]

You are welcome.  Even if it is, i would not try it.

[jayt93]

@lbendlin Thank you so much I was just trying to figure out how to upload it here!

[Ashish_Mathur]

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.

[lbendlin]

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