Forum Discussion
Ranking
Hi. Is there a way to Rank a specific column (GXT) but if there is an "Including" in the Category Column then keep those intervals together? I hope someone can help me. Thanks a bunch! Example: ...
bswank31 if you noticed it adds a new column in PQ "GTX for Rank" and it is based on the assumption that "Including" row is next to the row it belongs to, basically it is a way to group otherwise there no logical way to find out "including" belongs to which group. If there is another way to do that then that would be the logic for "GTX for Rank", you have to work with your data and determine what logic works best.
I had someone helping me with this a few days ago and this code gave me what I needed until I applied it to my larger data source. Below is the code and output of the code.
Ranking =
VAR current_category = SELECTEDVALUE( Sheet1[Category])
// Gets the value of the category on the current row.
VAR gxt = SELECTEDVALUE(Sheet1[GXT])
// Gets the value of GXT on the current row.
RETURN
IF( current_category = "Including",
CALCULATE( RANK.EQ( gxt, Sheet1[GXT], DESC), FILTER( ALLEXCEPT(Sheet1, Sheet1[Date]), Sheet1[Category] = "Including")),
// Ignore any rows that say "Including".
CALCULATE( RANK.EQ( gxt, Sheet1[GXT], DESC), FILTER( ALLEXCEPT(Sheet1, Sheet1[Date]), Sheet1[Category] <> "Including")))
// Using the ALL() function to ensure the whole table is condsidered when evaluating each row.
// Uning FILTER() to ensure "Including" is being excluded.Is there a way to get any of this logic into what you gave me to make it work so the ranking will work how I want?