Qlik sense - How to calculate Total & Percentage of Totals in Table

[SK28]

Hi guys,

Need some Suggestion on how to calculate Totals & Percentage of Totals in Qliksense Table ( Not Pivot Table) 

I have a requirement to calculate Totals & Percentage of Totals as shown below.(rows with are in bold)

here is the excel formal:

Totals = "=SUM(D32:D40)" Total for Del column is 9.43+0.41+0.41+0.90+1.04+1.35+0.46+15.41+0.25 = 29.65

Total percentage = IF($H$41>0,D41/$H$41,"")  the 29.55/160.17 = 18.5% (Total of Del/Total of Target)

Similarly for all other columns

Can you please help me on this, Thanks in Advance

City	Del	Los	P	Total	Target	Solu
Amst	9.43	24.89	0.81	35.14	38.12	-2.98
C	0.41	1.52		1.93	3.32	-1.39
EAS	0.41	7.22	0.04	7.67	10.00	-2.33
LAS	0.90	3.85	0.04	4.80	8.15	-3.35
MP	1.04	3.37	0.12	4.52	4.64	-0.12
U	1.35	25.32	0.75	27.42	28.39	-0.97
US	0.46	3.62		4.08	7.83	-3.75
Guj	15.41	28.29	3.32	47.03	54.92	-7.89
Holland	0.25	4.19	1.23	5.67	4.80	0.87
Total (need help here)	29.65	102.27	6.32	138.25	160.17	-21.92
Percentage of Target (Need help here)	18.5%	63.9%	3.9%	86.3%	100.0%	13.7%

[vinieme12 · Accepted solution]

As below

Main:
Load * inline [
City,Del,Los,P,Total ,Target,Solu
Amst,9.43,24.89,0.81,35.14,38.12,-2.98
C,0.41,1.52, ,1.93,3.32,-1.39
EAS,0.41,7.22,0.04,7.67,10.00,-2.33
LAS,0.90,3.85,0.04,4.80,8.15,-3.35
MP,1.04,3.37,0.12,4.52,4.64,-0.12
U,1.35,25.32,0.75,27.42,28.39,-0.97
US,0.46,3.62, ,4.08,7.83,-3.75
Guj,15.41,28.29,3.32,47.03,54.92,-7.89
Holland,0.25,4.19,1.23,5.67,4.80,0.87
];

DummyDim:
Load * inline [
Dim
1
2
3
];

In Chart

Dimension

=Pick(Dim,City,'Total','Total%')

Measures

Del

=Pick(Dim
,num(Sum(Del),'#,##0.00')
,num(Sum(Del),'#,##0.00')
,num(Sum(Del)/Sum(TOTAL Target),'#,##0.01%')
)

Los

=Pick(Dim
,num(Sum(Los),'#,##0.00')
,num(Sum(Los),'#,##0.00')
,num(Sum(Los)/Sum(TOTAL Target),'#,##0.01%')
)

repeat the same for the rest of the measure and set number  formatting to measure expression

[SK28]

Hi,

This works thanks, But the problem is when the user selects any value in CITY dimension, the total & Total% are disappearing

example user clicks on US

US 0.46 3.62 is showing, when User clears the city filter, total & total% are not showing up, once he clears all the filters then it's showing.

Is there a way to show the TOTAL & TOTAL% even with & without selections?  

[SK28 · Accepted solution]

Here is the Solution:

Step -1 Add Action Select all values in Dim & "LOCK IT"

Step 2 - Add this script

TAG FIELDS "Dim"  With $HIDDEN; 

doing this we can achieve the solution - Dim hidden, all values selected & locked , Clear all doesnt work for DIM