How to get a subtotal line in my table?

[styleofme]

Hello,

I have this simple table, with a dimension "Customer" and 2 measures : https://imgur.com/a/GwhbM2U 

My Customer dimension is set with a limitation of top 10 and with "Others" (out of top 10) displayed. I would like to add the subtotal of my 2 measures for my top 10 customers in my table. The goal would be to have this subtotal row between the top 10 Customer rows and my "Others" row.

Do you know if this is possible? I could also make use of a pivot table if needed.

Thank you in advance!

[Ravi_Nagmal]

@styleofme can you share what is ticked in below presentation setting

[Aditya_Chitale]

@styleofme ,

You can achieve this using script. You cannot do this on frontend directly.

For this create a resident load from Fact table and use recno() with order by clause to load only top N records in resident table. Now create an additional field as "Sort" in both resident and fact tables and assign it a value in both tables. Note that the value of this field should be greater in resident table that fact table.

Finally in frontend table chart use this field to achieve desired sort order.

You can refer below screenshot of sample script and output (I have sub-totaled top 3 rows hence recno()<=3) :

Output:

Regards,

Aditya

[vinieme12]

A Small inline table will do the trick

example

//Sample raw data 
data:
Load *,recno() as Sales inline [
Cust
A
B
C
D
E
F
G
H
I
J
K
L
M
N
];

//Inline table to group subtotal and Others
Temp:
Load * inline [
dim,so_dim
Customer,1
SubTotal,2
Others,3
];

Raw Data

IN a Table Chart

Dimension:

=pick(so_dim,Cust,dim,dim)

Measure:

=Pick(so_dim
,aggr(if(rank(Sum(Sales),4,2)<=10,sum(Sales)),Cust)   // Expression for Top 10 Customers
,sum(total aggr( if(rank(Sum(Sales),4,2)<=10,sum(Sales)),Cust))   // Expression for Subtotal of Top 10 Customers
,sum(total aggr( if(rank(Sum(Sales),4,2)>10,sum(Sales)),Cust))   // Expression for SubTotal of Customers ranking beyond 10
)

[Ahidhar]

try it in Pivot table like this

tab1:
load Customer,sum(Sales) as sales group by Customer;
load * inline
[
Customer,Sales
1,10
2,20
3,30
4,40
5,50
6,60
7,70
8,80
9,90
10,100
11,110
];
NoConcatenate
tab2:
load *,
if(recno()<10,'Top10','Others') as status
resident tab1 order by sales desc;drop table tab1;