Forum Discussion

bswank31's avatar
bswank31
Helper II
3 years ago

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: ...
Need Help
lbendlin's avatar
lbendlin
Super User
3 years ago
if there is an "Including" in the Category Column then keep those intervals together?

It is not clear what you are trying to achieve. What is the expected outcome based on your sample data?

bswank31's avatar
bswank31
Helper II
3 years ago

Yes. If category has "Including" then keep that interval with the one it belongs with:

I would like to be able to rank the highests GXT. If there is an "Including" then give it the same rank as the interval it belongs to. Here is the code I am using but it is not quite right:

Ranking 1 =
VAR current_category = SELECTEDVALUE( 'Sheet1 (2)'[Category])
VAR gxt = SELECTEDVALUE('Sheet1 (2)'[GXT])
RETURN
IF( current_category = "Including",
    CALCULATE( RANK.EQ( gxt, 'Sheet1 (2)'[GXT], DESC), FILTER( ALLEXCEPT('Sheet1 (2)', 'Sheet1 (2)'[Date],'Sheet1 (2)'[Site]),'Sheet1 (2)'[Category] = "Including")),
    CALCULATE( RANK.EQ( gxt, 'Sheet1 (2)'[GXT], DESC), FILTER( ALLEXCEPT('Sheet1 (2)', 'Sheet1 (2)'[Date], 'Sheet1 (2)'[Site]),'Sheet1 (2)'[Category] <> "Including")))
(This was from another person helping me)
 
The output isn't putting the includings where they belong and the ranking is all messed up. It also is not filtering correctly when I select my years slicer.

Your help is much appreciated! 


 

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    sorry, still not clear to me, especially what you mean by "the interval it belongs to" - please explain what that means.

     

    What is the expected outcome based on your sample data?

    • bswank31's avatar
      bswank31
      Helper II
      3 years ago

      What I am looking for is to create a Ranking from my GXT column and if my category column Contains "Including" to give it the same rank as the interval it belongs with: See how each color belongs in the same interval?

       

      This first table works and is what I want but when I apply it to my table with more sites and years (See below table) it gets all messed.

      This table is not filtering correctly when i select the year and the ranking is not working. 

      I hope this makes more sense.

      thanks,

      • lbendlin's avatar
        lbendlin
        Super User
        3 years ago

        Nowhere in your source data does it say "interval" .  What is an interval?  Is it a combination of Site, Project and Hole?