Hi ,
I have searched this forum but can not find the solution for Top N Customers with total for others also.
I have created the straight table which shows Top Revenue Customers for different states( two dimension Customer and State). Even I have created variable for top customer so user enter any number and he gets result, below is expression:
Total Sales:
Num(Sum(Aggr(if(rank(sum([Cust Sales Rev]))<=$(vTop_Cust),Sum([Cust Sales Rev])), [Cust Name])),'#,##0;(#,##0)')
CA State Sales:
Num(Sum(Aggr(if(rank(sum([Cust Sales Rev]))<=$(vTop_Cust),Sum({<[Cust State] = {'CA'}>}[Cust Sales Rev])), [Cust Name])),'#,##0;(#,##0)')
NY State Sales:
Num(Sum(Aggr(if(rank(sum([Cust Sales Rev]))<=$(vTop_Cust),Sum({<[Cust State] = {'NY'}>}[Cust Sales Rev])), [Cust Name])),'#,##0;(#,##0)')
and more 6 states:
For Eg if user selected top 5 , I get o/p as shown
Cust Name Total Sales Rev CA Sales Rev NY Sales Rev
Totals 9000 4000 5000
A 2000 1000 1000
B 1000 200 800
C 1000 800 200
D 3000 1500 1500
E 2000 500 1500
but user wants to show remaining other totals also
Other 10000 3000 7000
How to achieve last piece Total for OTHER remaining customers using set expression.
I tried also different way but in that I am getting complete column total and others total but not TOP N total.
Any help is appreciated.
Thanks
-V
