Break ties in RANKX Powerpivot formula

Question

I can rank my data with this formula, which groups by Year, Trust and ID, and ranks the Areas.

rankx(
      filter(Table,
          [Year]=earlier([Year])&&[Trust]=earlier([Trust])&&[ID]=earlier([ID])),
              [Area], ,1,Dense)

This works fine - unless you have data where the same Area appears more than once in the same group, whereupon it gives all rows the rank of 1. Is there any way to force unique rank values? So two rows that have the same Area would be given the rank of 1 and 2 (in an arbitrary order)? Thank you for your time.

Answer 1

Assuming you don't have duplicate rows in your table, you can add another column as a tie-breaker in your expression.

Suppose your table has an additional column, [Name], that is distinct between your multiple [Area] rows. Then you could write your formula like this:

= RANKX(
      FILTER(Table,
          [Year] = EARLIER([Year]) && 
          [Trust] = EARLIER([Trust]) &&
          [ID] = EARLIER([ID])),
      [Area] & [Name], , 1, Dense)

You can append as many columns as you need to get the tie-breaking done.