how can i group all rows prior to a certain row as those rows?

[nataliad18]

So, I realise the subject is very confusing, so I will try to explain here.

I have cross-tabbed data set and now have something like this:

ID       Name

1        New York

2        Boston

3        California
4        America
5        Singapore

6        Hong Kong

7        Asia

8        London

9        Paris

10      Europe

So, I have three Metrics: Asia, Europe and America.

How can I make it dynamic that ALL PRIOR to Asia/Europe/America are grouped as that region? (so in this case 1,2,3 would be America, 5,6 would be Asia and 8,9 would be Europe?)

Any ideas are welcome! I was considering making another column with an if formula, but it seems a bit long, and it feels like there should be a more efficient way that is less prone to error.

Thanks!
Natalia 

[ShankerV]

Hi @nataliad18 

This can be achieved with the help of lookup table.

As we need to identify how New York related to America.

Many thanks

Shanker V

[ShankerV]

Hi @nataliad18 

One use case for your scenario.

[BS_THE_ANALYST · Accepted solution]

@nataliad18 one way:

[nataliad18]

Thanks both!! Accepted @BS_THE_ANALYST solution as I need it to be a little bit more dynamic, as not all cities every run are the same and it works perfect! Thanks so much!!!