Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic Rank Measure

I'm trying to create a measure that re-calculates the rank in the power pivot table everytime the user deselects a value or changes the group by row labels. For example, per table below, there are 3 ...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    Anonymous 

    Thank you for sharing the extended data set.  I didn't really like the -1 either and with the extended data set it doesn't work because there are many companies not represented on a given quote and each of them was returning BLANK which is treated as a 0 in the ranking.  This is fine if you do DESC but because we needed ASC it was counting them so the first company in Quote 1 that got a ranking was getting like 24.

    What we needed it to filter the list of companies that are getting ranked to just those with Rate so here we go.

    Ranking = 
IF (
    ISFILTERED ( Quotes[Quote] ),
    RANKX ( FILTER ( ALL ( Quotes[Company] ), [Rate Total] ), [Rate Total],, ASC ),
    AVERAGEX (
        VALUES ( Quotes[Quote] ),
        RANKX ( FILTER ( ALL ( Quotes[Company] ), [Rate Total] ), [Rate Total],, ASC )
    )
)

    This portion of the measure (FILTER ( ALL ( Quotes[Company] ), [Rate Total]) returns all companies that have a rate in the quote context and if the Quote is not visable we still have quote context because we are iterating over the list of quotes then averaging the Ranking.

  • Anonymous's avatar
    Anonymous
    6 years ago

    jdbuchanan71 Thanks a lot. Worked like a charm once I replaced All with AllSelected to make sure that the user selects on CompanyName was also taken into consideration by the measure.

jdbuchanan71's avatar
jdbuchanan71
Super User
6 years ago

Anonymous ,

I believe the measure should work in Excel as well, it's just DAX code.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

jdbuchanan71 Unfortunately, when I type this out in the new measure window, I get the following error:

 

"Failed to resolve name 'ISINSCOPE'. It is not a valid table, variable, or function name."

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    6 years ago

    Ahh, sorry about that.  Try it with ISFILTERED.

    Ranking = 
IF (
    ISFILTERED ( Quotes[Quote] ),
    RANKX ( ALLSELECTED ( Quotes[Company] ), [Rate Total],, ASC ),
    AVERAGEX (
        VALUES ( Quotes[Quote] ),
        ( RANKX ( ALLSELECTED ( Quotes[Company] ), [Rate Total],, ASC ) )
    )
)

     That works for me in Excel

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      jdbuchanan71 Very close to being perfect. I noticed in my data that sometimes there is a possibility that one of the quotes may not have a Company C rate but instead a Company D. For example:

      QuoteCompanyRate
      1A500
      1B600
      1C700
      2A700
      2B500
      2D600

      Unfortunately, the measure doesn't work. It looks like it is ranking from the distinct list of company names:

      QuoteCompanyRate TotalRankingCorrect Rank
      1A50021
      1B60032
      1C70043
      2A70043
      2B50021
      2D60032
      • jdbuchanan71's avatar
        jdbuchanan71
        Super User
        6 years ago

        Anonymous 

        I think we are getting caught by the "blank" company on a quote where each company doesn't exist being ranked #1 but because we don't want to see that we can just shift the rank down.

        Ranking:=IF (
    ISFILTERED ( Quotes[Quote] ),
    RANKX ( ALLSELECTED( Quotes[Company] ), [Rate Total],, ASC )-1,
    AVERAGEX (
        VALUES ( Quotes[Quote] ),
        ( RANKX ( ALLSELECTED( Quotes[Company] ), [Rate Total],, ASC ) )-1
    )
)

        This gave me correct results across the sample but give it a test on your side.