Forum Discussion
Post ProdigyRANKX always returns 1
I've already inspected the other threads with the same dilemma, but most of them are running with SUMs of a numeric column. I'm trying to get the rank of an item through the number of instances it has appeared on the table (ie: COUNTROWS). Using the formula below, I always get a 1 on my Rank.
I'm pretty sure I've no problems with the [# of Violations] not having a CALCULATE since it's already a calculated measure, which seems to be the cause of the problem most of the posts for RANKX. I'd like to point out that I'm also using Page-level filters, unsure if this will affect the measure in any way.
' Measure
# of Violations = COUNTROWS(Violations)
' Measure
Rank =
RANKX(
ALLSELECTED('Violations'),
[# of Violations], , DESC, Dense
)
Below is the expected result from the sample data:
| Violation | 2017 | Rank | 2018 | Rank |
| A | 6 | 1 | 7 | 2 |
| B | 4 | 3 | 8 | 1 |
| C | 5 | 2 | 5 | 3 |
| Site | Violation | Inspected | Year Inspected |
| A1 | A | 1/1/2017 | 2017 |
| A1 | B | 1/1/2017 | 2017 |
| A2 | C | 1/1/2017 | 2017 |
| A3 | A | 1/1/2017 | 2017 |
| A1 | B | 1/1/2017 | 2017 |
| A2 | C | 1/1/2017 | 2017 |
| A3 | A | 1/1/2017 | 2017 |
| A1 | B | 1/1/2017 | 2017 |
| A2 | C | 1/1/2017 | 2017 |
| A3 | A | 1/1/2017 | 2017 |
| A1 | B | 1/1/2017 | 2017 |
| A2 | C | 1/1/2017 | 2017 |
| A3 | C | 1/1/2017 | 2017 |
| A1 | A | 1/1/2017 | 2017 |
| A3 | A | 1/1/2017 | 2017 |
| A1 | A | 1/1/2018 | 2018 |
| A2 | B | 1/1/2018 | 2018 |
| A3 | C | 1/1/2018 | 2018 |
| A1 | A | 1/1/2018 | 2018 |
| A1 | B | 1/1/2018 | 2018 |
| A2 | C | 1/1/2018 | 2018 |
| A3 | A | 1/1/2018 | 2018 |
| A1 | B | 1/1/2018 | 2018 |
| A3 | C | 1/1/2018 | 2018 |
| A1 | A | 1/1/2018 | 2018 |
| A2 | B | 1/1/2018 | 2018 |
| A1 | C | 1/1/2018 | 2018 |
| A1 | A | 1/1/2018 | 2018 |
| A2 | B | 1/1/2018 | 2018 |
| A3 | B | 1/1/2018 | 2018 |
| A1 | B | 1/1/2018 | 2018 |
| A2 | A | 1/1/2018 | 2018 |
| A3 | A | 1/1/2018 | 2018 |
| A1 | B | 1/1/2018 | 2018 |
| A2 | C | 1/1/2018 | 2018 |
Would like to know how to solve this. Any input is definitely appreciated.
Hi olimilo
A tiny change in your formula is needed :smileywink:
Rank = RANKX ( ALLSELECTED ( Violations[Violation] ), [# of Violations],, DESC, DENSE )
5 Replies
- Zubair_Muhammad
Community Champion
Hi olimilo
A tiny change in your formula is needed :smileywink:
Rank = RANKX ( ALLSELECTED ( Violations[Violation] ), [# of Violations],, DESC, DENSE )
- Zubair_Muhammad
mattbriceSolution Sage
Please try replacing your ALLSELECTED statement with this
ALLSELECTED('Violations'[Violation] )
olimiloThanks for the help everyone! Although I find it weird that I had to indicate the column I'm going to be working on. I thought that using it on the measure would be enough.
- Ashish_Mathur
Super User
Hi,
This too should work
=RANKX ( ALL ( Violations[Violation] ), [# of Violations],, DESC, DENSE )
I dont think the ALLSELECTED function is required.
