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

# Finding Common Fields based on Parameter

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?

• ###### 1. Re: Finding Common Fields based on Parameter

Sean,

I think it was mostly there.

I tried the following and it seemed closer:

NOT [Selected City] AND

CASE [Comparison Level]

WHEN "All" THEN True

WHEN "Region" THEN [Region] = {FIXED : MAX(IF [Selected City] THEN Region END)}

WHEN "State" THEN [State] = {FIXED : MAX(IF [Selected City] THEN State END)}

ELSE FALSE

END

Having "FIXED" without anything to the left of the colon indicates

setting the value for the whole dataset.

Please see workbook v10.5 attached in the Forum Thread:

Finding Common Fields based on Parameter

1 of 1 people found this helpful
• ###### 2. Re: Finding Common Fields based on Parameter

Aargh! I mean, Yay!  I mean, Thanks!  That did the trick.  Definitely something I should have been able to figure out on my own, but I really appreciate you taking the time to point me in the right direction.

• ###### 3. Re: Finding Common Fields based on Parameter

Sean,

Haha!

Glad that was helpful.

All the best.