Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Bucharest on Sept 18th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
DwayneO
Contributor
Contributor

divide dimension field by total row in a pivot table

Hello,
How can I divide a dimension field in a row by the total dimension row in a pivot table?

For example, in my pivot table I have a dimension called 'Zone'  (values A, B,C) that displays vertically and another dimension called 'Delivery Type' (Values - Email, Fax, Image - Only, Manual Print, etc.) that is displayed horizontally like this with a calculation of Sum(Measure):

Zone Delivery TypeEmailFaxImage - OnlyManual PrintManual BundleManual PackageAutomated PrintAutomated BundleAutomated EmailTotal
A  12,58525916,0072,58712713,45801,78946,587
B  70,78243,36223,22494,35879,4538,87847,8185,1604,776377,811
C  10,50951018,8379,5474,37394,7232,4301,62752,565
          Grand Total: 476,963

 

I want to show a percentage of each delivery type for a zone divided by the row total for a zone (example  (zone A  of Delivery Type Email 12,585 / Row Total of Zone A 46,587 ) = 27.01%)  However, I can't get it work.

My calculation of SUM(Measure) / SUM(TOTAL Measure) is giving me the percentage of each delivery type divided by the entire total (example  (zone A  of Delivery Type Email 12,585 / Grand Total 476,963 ) = 2.64%)

Here is my desired result:

ZoneDelivery TypeEmailFaxImage - OnlyManual PrintManual BundleManual PackageAutomated PrintAutomated BundleAutomated EmailTotal
A 27.01%0.05%0.02%34.36%5.55%0.27%28.89%0.00%3.84%100.00%
B 18.73%11.48%6.15%24.97%21.03%2.35%12.66%1.37%1.26%100.00%
C 19.99%0.97%35.84%18.16%8.32%0.02%8.99%4.62%3.10%100.00%

 

Any ideas would be greatly appreciated.  Thanks.

 

Labels (1)
1 Solution

Accepted Solutions
DwayneO
Contributor
Contributor
Author

I was able to figure out the solution for my issue. 

New Expression:

Sum(Measure) / Sum(TOTAL <Zone> Measure)

 

View solution in original post

1 Reply
DwayneO
Contributor
Contributor
Author

I was able to figure out the solution for my issue. 

New Expression:

Sum(Measure) / Sum(TOTAL <Zone> Measure)