Forum Discussion
Rank based on Ranked Index
I am trying to find the average time spent in status or with a set issue. I have created a hierarchical sort in the Power Query Editor based on the issue category, initiative key, and the update date. Then, I added an index column, which I created a ranked index for each Initiative key, as seen here:
resulting in the table seen here (sensitive data removed):
I would like to create a ranked column for each consecutive issue following the same process here and another for each consecutive R/Y/G status, that resets for each initiative key . Perhaps a "Ranked Issue" and "Ranked Status" column?
Something like this:
(Apologies for my hasty photo editing skills)
Using the rankings, I believe I can ultimately calculate the average maximum ranking of issue or status to show the average amount of weeks they impact projects (initiative keys). Every entry is conducted weekly to append the table, and the rank could artificially serve as a dynamic counter of those consecutive weeks. I would like to show cards similar to these which I previously mocked up:
Thank you.
3 Replies
- amitchandak
Super User
Anonymous , Create two new columns
Rankx(filter(Table, [Issue Id] = earlier([Issue_id])) , [Index],asc,dense) //you use date inplace of index
and
Rankx(filter(Table, [Issue Id] = earlier([Issue_id]) && [Issue category short] = earlier([Issue category short]) ) , [Index],asc,dense) //you use date inplace of index
This does work with the change in issues. However, I cannot replicate it for the status changes because if on initiative key has multiple issues assigned, they will have repeated rows for each extra issue. This causes the status ranking counter to continue rather than resetting at the change of issue, as seen below for Initiative Key 213.
The R/Y/G column drives the "On Track Status" column, and we see that there are really only 11 consecutive weeks of "Missing" status, rather than 22.
