CITY/MEMBER Dimension wise Cumulative %tile Calculation

[#1]

Dear All,

i have table with CITY and MEMBER Dimension as below:

MEMBER	CITY	TURNOVER
A	MUMBAI	100
A	DELHI	200
B	DELHI	100
B	MUMBAI	300
B	PUNE	500
C	DELHI	50
C	MUMBAI	250
C	PUNE	600
D	DELHI	25
D	MUMBAI	50
D	PUNE	200
E	MUMBAI	25
E	PUNE	125
E	DELHI	225
F	MUMBAI	110
F	PUNE	150
F	DELHI	300

i have make table which show Memberwise Turnover along with Cumulative %tile ( i have used relative option with full accumulation)

CITY Selected : DELHI

MEMBER	Sum(TURNOVER)	Cumlative %tile
	900	100.00%
D	25	2.78%
C	50	8.33%
B	100	19.44%
A	200	41.67%
E	225	66.67%
F	300	100.00%

In the Above table you can see Member 'A' have '41.67%'  on DELHI City.

Now below is my requirement.

suppose select a Member 'A' then i want  to see CITY wise Turnover of a MEMBER like below:

CITY	Sum(TURNOVER)
	300
MUMBAI	100
DELHI	200

In the above table i want to added a more column name 'Cumulative' which show the Member's Cumulative position which we seen on above table i.e. 41.67% . the output will as below:

CITY	Sum(TURNOVER)	Cumlative
	300	0.00%
MUMBAI	100	<what ever value as based of MUMBAI>
DELHI	200	41.67%

I am also attaching the sample QVW for ready reference.

Kindly help.

Thanks & Regards,

Jitendra K Vishwakarma

[#2]

Kindly Ignore previous image.

I think,this is what you want.

[#3]

That is also wrong what i dont want

below is image what i want .. first two table(DELHI and MUMBAI) is reference what calculation needs to be done for MEMBERS  and the last third table is were and how i want to show when i selecte MEMBER 'A'.

[#4]

Hi,

i am able to get RANK() base SUM can we use some more that what rank() is of the MEMBER get SUM to his RANK()

1) Calculating RANK. : min(Aggr(rank(sum({<MEMBER>} TURNOVER)),CITY,MEMBER))

2) top 2 RANK wise SUM :sum({1}aggr(if(rank(sum({<MEMBER>} TURNOVER))<=2,sum({<MEMBER>} TURNOVER)), CITY,MEMBER))

i try to use this way but did not work( Merge the both)

sum({1}aggr(if(rank(sum({<MEMBER>} TURNOVER))<= min(Aggr(rank(sum({<MEMBER>} TURNOVER)),CITY,MEMBER)),sum({<MEMBER>} TURNOVER)), CITY,MEMBER))