Forum Discussion
Helper IRANKX positive to Negative
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 |
10 Replies
v-danhe-msftMicrosoft Employee
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
- tex628
Community Champion
Do you want to filter region by top values in Var to Target?
MattN4Thanks, 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.
- MattN4
Manged to sort it by selecting ALLSELECTED opposed to ALLEXCEPT.
Thanks for your input.
- tex628
Anonymous ,
Can you give a brief explaination of the issue, maybe provide some images and expected outcome?
/ J
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.
