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...
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.
I believe I solved the running counter problem, seen below:
Is there a way to calculate the average maximum rank for the new columns?