If I have understood your question correctly then you are trying to know the rank of the customer at different level.
At My Customer level its right, please refer to the below screenshot for the same.
but at state level your desired output is wrong. Your selected customer ranks 2nd in the State with the volume of 275. Your output says rank should be 3 and volume should be 358. Please explain that part.
Similarly at the region level, selected customer stands at Rank 4 and volume remains same which is 275 where you have mentioned it as Rank = 2 and volume = 400. Please explain that as well.
At the India Level, you will get the same ranking as you got for customer since all customers belong to India. Please refer to the below screenshot. As per your output Rank is 1 and Volume is 580 for the selected customer. This Rank belong to JC-MU-01. If you want details of customer with higher volume then you can simply get it we don't need to involve the country here.
Where as actual JC-HR-03, National ranking is 5 and volume is 275 (no change at any level).
Let me know what are your thoughts on this and how you have derived the desired output at the State, Region and National Level.
I am also attaching the workbook in next post. Let me know if this helps.
Thanks and Regards,
Thanks but you have not checked my output however I found a way.