3 Replies Latest reply on Dec 13, 2019 9:13 AM by swaroop.gantela

    Finding Common Fields based on Parameter

    Sean Jeffery

      Good day all,

       

      This seems like it would be such a common request, I'm sure it's been asked and answered before, but so far Google has been no help resolving this for me. I've attached a workbook using Superstore data to help illustrate what I'm trying to accomplish.

      I've created a parameter that allows the user to select a City.  A table is then generated showing top 5 and bottom 5 products sold in that city.  So far so good.

      I've created a second parameter which allows the user to select a Comparison Level with the following options.

      • All Locations
      • Region
      • State

      What I would like is to compare the sales of the Top 5 and Bottom 5 articles in the selected city to the average sales of those same articles in the entire country, or the same Region or State as the selected city.

      I've got it working for All Locations:

      But can't figure out the other two options.

      I'm using the following calculated field:

      but I'm pretty sure the LOD logic isn't what I'm looking for.

       

      How to you find Cities with the same Region or State as a City selected in a Parameter?