Forum Discussion

mim's avatar
mim
Icon for Advocate V rankAdvocate V
9 years ago
Solved

Rank Based on measures with row context.

Hello

 

I am trying to build a measure that ranks the values of another measures ( see attached picture), I know how to do as a calculated column, as you can use the row context ( [TOSTR FORECAST DATE]=EARLIER([TOSTR FORECAST DATE]), but i need to do it as a measure in a pivot table,

 

any idea how to do that, as measures don't support row context, I am using Excel 2013

 

 

 

Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    mim, ImkeF, Anonymous - interesting problem.

     

    Anonymous

    I agree with your logic in the last post, and for Excel 2013 I would write the measure like this:

     

     

    =
IF (
    HASONEVALUE ( MyTable[ID] ),
    RANKX (
        FILTER (
            ALL ( MyTable[ID] ),
            [TOSTR FORECAST DATE]
                = CALCULATE ( [TOSTR FORECAST DATE], VALUES ( MyTable[ID] ) )
        ),
        MyTable[ID],
        VALUES ( MyTable[ID] )
    )
)
    1. Added a HASONEVALUE check to allow evaluation only for single IDs
    2. Within FILTER, the measure for the currently iterated ID is [TOSTR FORECAST DATE], and this is compared with the measure from the original filter context CALCULATE ( [TOSTR FORECAST DATE], VALUES ( MyTable[ID] ) ).
      Putting VALUES ( MyTable[ID] ) as a filter argument effectively undoes the context transition.
    3. Ranking is just based on ordering of IDs, descending by default. Can be tweaked.

     

    Sample workbook here in case useful.

     

    Cheers,

    Owen :)

  • Anonymous's avatar
    Anonymous
    9 years ago

    Wait, are you trying to tell me you actually understand the 3rd argument to RANKX?  NOBODY DOES!!  :D


    Totally based on Owen's really good thinking, *I* would probably do this slightly differently to avoid the 3rd param:

    Rank of ID among those with same TOSTR FORECAST DATE 2:=IF (
    HASONEVALUE ( MyTable[ID] ),
      RANKX (
        FILTER (
          ALL ( MyTable[ID] ),
          [TOSTR FORECAST DATE] = CALCULATE ( [TOSTR FORECAST DATE], VALUES ( MyTable[ID] ) )
        ),
        CALCULATE( VALUES( MyTable[ID] ) )
      )
    )


8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago
    Talk about a time I wish I had VAR/RETURN to use.

    Just making sure I understand -- and helps my to clarify for somebody with a good idea...

    Column 1 is some ID.
    Column 2 is the result of a measure (happens to return a date).
    Column 3 is a rank of some unseen other measure, but only against rows with same column 2 Date.

    Ya?
    • ImkeF's avatar
      ImkeF
      Icon for Community Champion rankCommunity Champion
      9 years ago

      Hi mim,

      just to check understanding: You need the Rank as a measure, but the [TOSTR FORECAST DATE] is a column, so Rank per [TOSTR FORECAST DATE]-day?

       

      Or is the date a measure as well?

       

       

      • mim's avatar
        mim
        Icon for Advocate V rankAdvocate V
        9 years ago

        yes TOSTR FORECAST DATE is a measure basically  if there is one unique date then the rank will be 1, if the same date is repeated 5 times then the rank should 1,2,3,4,5 the order is not important