RANKX function issue

[Babken Hakobyan]

[FONT=&quot]Hello,[/FONT]
[FONT=&quot]In PowerPivot I have the following measure: [/FONT]
[FONT=&quot]Sold:=CALCULATE(COUNTA(Database[Status]),Database[Status]="SOLD",Database[Range Status]=1)[/FONT]
[FONT=&quot]Based on that measure I created the following formula:[/FONT]
[FONT=&quot]Sales Rank Score:=RANKX(ALLSELECTED(Database[Agent Name&ID]),[Sold],,ASC)*0.25. So basically i want to have the agents ranked and a relevant weight (in this case it's 25%) applied based on the "Sold" values. Then I create a PivotTable displaying agents in the row field and "Sales Rank Score" as values. I then create a slicer containing ZIP Codes. So now I sort the values in the PivotTable and then select the top ten agents. The scores automatically change and display ranks calculated just for those top 10 agents. Moreover, the RANKX function totally ignores what criteria lie in the "Sold" measure. Then a more strange thing happens: when I clear the top 10 filter, the scores are calculated for all ZIP codes and not just the one selected in the slicer. So my questions are the following:[/FONT]
[FONT=&quot]1.How can I make the RANKX function ignore the top 10 filter in the PivotTable and calculate the ranks only based on the ZIP code selected in the slicer?[/FONT]
[FONT=&quot]2. How can I make the RANKX function take into account the criteria in the "Sold" measure?[/FONT]
[FONT=&quot]I hope I was clear enough. If not, please don't hesitate to ask.[/FONT]
[FONT=&quot]Thank You in advance.[/FONT]

 

[Matt Allington]

Ten allselected function is sensitive to filters in your pivot table. Why are you using allselected and not all?

 

[Babken Hakobyan]

Ten allselected function is sensitive to filters in your pivot table. Why are you using allselected and not all?

I used the ALL function before, but it populates the rank among all items ignoring the ZIP code selected in the slicer.

 

[Matt Allington]

Then you should use allexcept, and specify it should ignore the zip.

 

[Babken Hakobyan]

Then you should use allexcept, and specify it should ignore the zip.

Thank You very much. I'll try it.