Hi all,
I have data of example below, in this data we have Country and City with Sales and Profit
My idea is to build a scatter plot with Sales against Profit. I would like use a button to select either Country or city so that the dimension of scatter plot based on user selection. Also, because there are too many record, I would like to display either top 5 Countries based their sales or top 5 City based on their sales.
I have no idea how to write the rank formula to get top 5 countries or top 5 city based on their sales. Also, how to write the formula to make the dynamic switch between top 5 Countries based their sales or top 5 City based on their sales with the use of button/variables. (I guess if statement?)
Data like below
x axis as sum(Sales)
Y axis as sum(Profit)
| Country | City | Sales | Profit |
| US | NYC | 37,733.28 | 333.28 |
| US | LAX | 18,141.00 | 1041 |
| US | SFO | 1,814.10 | 14.1 |
| UK | MAN | 5,830.00 | 50 |
| UK | LON | 3,279.45 | 379.45 |
| China | SHA | 1,457.50 | 1357.5 |
| China | PEK | 2,418.80 | 218.8 |
| Japan | TYO | 18,141.00 | 18041 |
| Japan | OSK | 24567 | 1432 |
