Pivot tables: totals across down over different column values

Q&A
1

My sample pivot table looks like:

cat v1 v2 v3
a 1 1 0
b 3 4 7
c 5 2 8

I would like to do (1) and (2) as per below. Note that I have tried using “Add Table Calculation / Table Across Down” but this does not work since v1, v2, v3 are treated as separate values

(1) - Be able to generate totals e.g.

cat v1 v2 v3 total
a 1 1 0 2
b 3 4 7 14
c 5 2 8 15
31

(2) - Be able to convert each cell value to a percentage of total

cat v1 v2 v3 total
a 3% 3% 0% 2
b 10% 13% 23% 14
c 16% 6% 26% 15
31

If anyone can advise how these outputs can be achieved in quicksight that would be greatly appreciated.

Many thanks in advance!

2

You will need to make a new calculated field for total.

total = v1 + v2 + v3.

Then you can add this field to your table.

Then for each of the other fields you can use percentOfTotal.

Finally you can add a total to the bottom but it will show all of your columns totals, not just the very last one.

image
image1285×1198 77.9 KB

3

Thanks for the suggestion.

Using your idea I managed to get my desired result with a small tweak i.e.

  1. created a calculated field that was sum of the other fields (as per your suggestion) e.g. Total = v1+v2+v3

  2. to calculate the percentage, I created a new calculated field for each field by dividing the value by the total e.g. v1_1 = v1/Total, v2_1 = v2/Total etc

Note that there is both a division bug and decimal precision bug which I was able to workaround by

  1. workaround for division bug: wrap with sum function e.g. sum(v1)/sum(Total). Without this the division result would be just outright incorrect

  2. workaround for decimal precision bug: multiply by float e.g. sum(v1)*1.0/sum(Total)*1.0. Without this the division result would round up to the nearest integer