Compared to standard competition ranking show in part 1, modified ranking does the opposite treatment for equally ranked record. Given we need to know how many repeats there are, we cannot use Multi-row Formula directly as before, as it computes in a top down order.
Instead, we will need to reverse the sorting order of the data, where the last term appear first
We know the last person should be ranked last, we can work backwards in that case.
If the current row is not equal to previous row (i.e. lower ranked measure), then there is no continued repeating ranking, we have reach the floor value for the rank, hence we will use the current Row ID. (Equal Ranking Count is used as an indicator in the formula)
Note: There is no rank 1 in modified rank if there is a joint first, i.e. the measure is only ranked higher if it is greater than the rest.
Modified competition ranking is much less common than Standard competition ranking, nevertheless, it is an option us to use if needed.
Looking for more guides, tips and tricks in Tableau or Alteryx? Go check out the other blog posts from the Data School.