# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Qlik sense - To calculate Total & Percentage of Totals in Table

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

 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%
Labels (6)

• ### Visualization

2 Solutions

Accepted Solutions
Champion III

As below

Main:
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:
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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Contributor III
Author

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

3 Replies
Champion III

As below

Main:
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:
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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Contributor III
Author

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?

Contributor III
Author

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