Here is the data set to check out. The main problem here is that which are states falls under coastal zone as per my knowledge....What if more states having more sales crossed 20000 dollars ???
I am confused with the answer how will it be. Here is the workbook attached. Please review it once.
What exactly have you tried so far? I'll be honest to say it doesn't feel like you've put in much / any effort here and this reeks like it's a homework type question.
Show me your thought process and I'll happily help you build on it.
I created Rank to State based on sales in descending order and put the reference lines based on amount (20000 dollars). But there are many states which are crossing the reference line. Out of these many states, i have to pick which one is coastal state ?
I'd suggest creating a map would help you see which of them are coastal states.
I think Tom has this well under control, but I might suggest you create a small Excel file of coastal states. You can Google this-- I found a list on Wikipedia; it even said if they were Atlantic or Pacific.
BLEND this file with your current data (by [State]), and filter the results, limiting it to just the coastal states you want (Atlantic, Pacific, or both).
You could also use the coastal indicator on color-- it's up to you and how you want your data to look.
Additionally, it seems pretty clear your color should NOT be set to SUM([SALES]); this is because you're looking JUST for states >$20,000. You probably need to create a small boolean calculation that highlights only when SUM([SALES]) >$20000.
Based on the data and the visualization you're presenting, clearly there are more states which are coastal, with sales of more than 20,000.
Perhaps the question you're trying to answer has more than one correct answer, allows for multiple choices, or maybe you're misreading the question? I can't really speak for why the author of the question prepared or presented it like that as I have no idea where it came from or what it relates to.