Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 Type | Fax | Image - Only | Manual Print | Manual Bundle | Manual Package | Automated Print | Automated Bundle | Automated Email | Total | ||
A | 12,585 | 25 | 9 | 16,007 | 2,587 | 127 | 13,458 | 0 | 1,789 | 46,587 | ||
B | 70,782 | 43,362 | 23,224 | 94,358 | 79,453 | 8,878 | 47,818 | 5,160 | 4,776 | 377,811 | ||
C | 10,509 | 510 | 18,837 | 9,547 | 4,373 | 9 | 4,723 | 2,430 | 1,627 | 52,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:
Zone | Delivery Type | Fax | Image - Only | Manual Print | Manual Bundle | Manual Package | Automated Print | Automated Bundle | Automated Email | Total | |
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.
I was able to figure out the solution for my issue.
New Expression:
Sum(Measure) / Sum(TOTAL <Zone> Measure)
I was able to figure out the solution for my issue.
New Expression:
Sum(Measure) / Sum(TOTAL <Zone> Measure)