Hello,
I am trying to rank the most recent year and quarter for us and our competitors so I can show trends for the most recent four quarters of data on a line chart, but cannot figure out how to make the ranking reset for each company. For example, this is what my data looks like before ranking:
| Company | Company Owner | Year | Quarter | Rank |
| Company 1 | My Company | 2019 | 1 | |
| Company 1 | My Company | 2019 | 2 | |
| Company 1 | My Company | 2019 | 3 | |
| Company 1 | My Company | 2019 | 4 | |
| Company 2 | Competitor | 2018 | 4 | |
| Company 2 | Competitor | 2019 | 1 | |
| Company 2 | Competitor | 2019 | 2 | |
| Company 2 | Competitor | 2019 | 3 | |
| Company 3 | Competitor | 2018 | 3 | |
| Company 3 | Competitor | 2018 | 4 | |
| Company 3 | Competitor | 2019 | 1 | |
| Company 3 | Competitor | 2019 | 2 | |
As you can see, the most recent quarters:
- Company 1: 2019 Q4
- Company 2: 2019 Q3
- Company 3: 2019 Q2
I would like the ranking to behave as followed:
| Company | Company Owner | Year | Quarter | Rank |
| Company 1 | My Company | 2019 | 1 | 4 |
| Company 1 | My Company | 2019 | 2 | 3 |
| Company 1 | My Company | 2019 | 3 | 2 |
| Company 1 | My Company | 2019 | 4 | 1 |
| Company 2 | Competitor | 2018 | 4 | 4 |
| Company 2 | Competitor | 2019 | 1 | 3 |
| Company 2 | Competitor | 2019 | 2 | 2 |
| Company 2 | Competitor | 2019 | 3 | 1 |
| Company 3 | Competitor | 2018 | 3 | 4 |
| Company 3 | Competitor | 2018 | 4 | 3 |
| Company 3 | Competitor | 2019 | 1 | 2 |
| Company 3 | Competitor | 2019 | 2 | 1 |
This way only way that ranks these appropriately is Max(TOTAL <Company> Year*4+Quarter) - Max(TOTAL <Year,Quarter> Year*4+Quarter) + 1, but I need the ranking as a dimension, not a measure. Any help is greatly appreciated!
