RANKX positive to Negative

[MattN4]

Hi,

When using the RANKX formula I get a gap between postive an negative numbers. How can I overcome this?

Measure is as follows;

Rank = RANKX(ALLEXCEPT(SPER,SPER[Region]),'Sales'[Var to Target],,DESC,Dense)

Gives me

Region	Var to Target	Rank
A	5%	1
B	1%	2
C	-1%	4

[Anonymous]

I ******* solved it at last. 

The problem is that when you filter, your measure returns "0" values, which negative numbers count after.

What you need to do is to apply a formula like to ignore blanks in your filter column like:

Filter(Allselected('Tablename'[Columnname]),[Measure]<>blank())

Let me know if this works for you guys. 

[tex628]

Do you want to filter region by top values in Var to Target?

[Anonymous]

@tex628 

[tex628]

@Anonymous , 

Can you give a brief explaination of the issue, maybe provide some images and expected outcome? 

/ J

[Anonymous]

Hi @tex628 

Same below problem what @MattN4   getting:

Region	Var to Target	Rank
A	5%	1
B	1%	2
C	-1%	4

rank value was skipping  when negative value starts in var to target column..

[tex628]

Generally speaking the solution to this is changing the ALLEXCEPT to an ALLSELECTED. Try that to begin with and can you post the current code of your ranking measure?
 
/ J

[MattN4]

Thanks, I can't share the pbix as it contains sensitive data.

Yes, there will be a filter. Basically in each region there would be several entries.

[tex628]

Try simply using the integrated topN filter on region with "Val to Target" in "by value"

[MattN4]

Manged to sort it by selecting ALLSELECTED opposed to ALLEXCEPT.

Thanks for your input.

[v-danhe-msft]

Hi @MattN4,

Based on my test, I got the correct result like the below picture, and I could not find your problem.

To find the cause as soon as possible, I would suggest you share pbix file if possible. If the report does contain sensitive data, please upload your report to your OneDrive and send the link to me via private message.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/r5zwighxdabok36/RANKX%20positive%20to%20Negative.pbix?dl=0

Regards,

Daniel He