Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I believe I solved the running counter problem, seen below:

    Is there a way to calculate the average maximum rank for the new columns?