Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP 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 (3)
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)