8 Replies Latest reply on Aug 7, 2018 11:51 AM by Shinichiro Murakami

    Look up rank in one table using value from another datasource

    Bryan Mills

      OK, I have a fun little project I'm looking to do but I need help in how to make it happen.

       

      In my data I calculate the # of people who filled out a survey by using the simple COUNTD(respondent_id) function that I call "Survey Respondents" and use in various places for various other calculations.

       

      I have another simple table of data that I copied into the workbook that shows the populations of all the cities in Washington State along with their rank, looking like this:

       

      rankcitypopulation
      1Seattle608,660
      2Spokane208,916
      3Tacoma198,397

       

      What I'd like to do is take that Survey Respondents measure and compare it to the table of data and have it show where you would rank if all your respondents in effect made one big "city" in the state with a population that equals the Survey Respondents measure. So let's say you have 321,000 survey respondents, you'd rank as #2 (between Seattle and Spokane) or if you had 207,000 you'd be 3rd behind Spokane, now ahead of Tacoma (who was #3 before this "new city" was placed in the list).

       

      Assume I need to look at the population table and then run down it until I find the value that is no longer larger than my value, look at the rank dimension to say "you are the 3rd largest city in Washington State" and that presumably uses some sort of lookup function that I'm not yet familiar with using, especially as the data are in 2 different places.

       

      Any ideas on where to start?