RANKX does not seem to be working for me.

Question

I have this data:

I would like the data in the Index column to not skip numbers but go in sequence -11112222222222233

I found definitions for the Supporting and Index columns in another location (sorry, I do not have the link). But here is what the responder advised:

The Supporting Column is created using:

=CALCULATE (
MIN ( Table[Index] ),
ALLEXCEPT ( Table, Table[id], Table[yr]))

Index column is created by:

=RANKX (ALL(Table),Table[Supporting Column],,ASC, Dense )

I cannot get the Index Column produce the mentioned result.

Please help.

Answer 1

@SteveKey99
I suggest you use the “Group By" feature in Power Query.

In Power Query, you may go to Home tab > Transform group > Group By, then choose "id" and "yr" for grouping basis, I set "Group Data" as a new column name and the "All Rows" for operation.
The deatiled settings and the result you may refer to the following image.

Go to Add Column tab > General group > Add an index column from 1, then you would get the result as the image below.

Please click on the expand toggle button located on the right of the "Group Data" column heading for expanding.
I tick the box of "qtr" and ""Amount" to expand data. The index number would be left after doing expanding.

At last, you could modify the column name for "qtr" and "Amount" as following screenshot.

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.