With a pivot table of values, want to add a single % column

Question & Answer
,
1

I have a single metric pivot table with row and column totals enabled. My user wants to see one of the columns divided by row total amount expressed as a percentage of the row total. Is there a way to do this in QS? If so, how?

Example data
region, col a, col b, col c, total, c as a % of total
North America, 5, 10, 15, 30, 50%
South America, 4, 46 0, 50, 8%
Pacific,1, 0, 0, 1, 0%
Africa, 0, 0, 19, 19, 100%
Total, 10, 56, 34, 100, 34%

2

You can try out table calculations on Pivot Table. In case you want to show both “Col C” and “% of total”, try re-adding “col c” to value field well and use that repeated column as a table calculation. Let me know if this works for you.

3

I don’t know that I have explained myself well, I used ‘col a’, ‘col b’, ‘col c’ to describe the pivot table. I don’t have three columns in my source data. I have one column with three values, ‘a’, ‘b’, and ‘c’. In this case c represents the number of entities that has completed all of the tasks (a is number of entities that have not completed a single task, and b represents the number of entities that has completed some but not all of the tasks). Management wants the raw data, and the % of C of the total.

Adding a pivot table calculation works great if I want to turn the entire pivot to be expressed as percentages.

4

Hi @cgreenacre ,

From your description, my understanding is that your raw data looks like this -

region, category, value
North America, a, 5
North America, b, 10
North America, c, 15
South America, a, 46
South America, b, 0
South America, c, 50

If this is incorrect, please provide an example of your raw table.

Based on the table above, to produce the example pivot table in your description -

Example data
region, col a, col b, col c, total, c as a % of total
North America, 5, 10, 15, 30, 50%
South America, 4, 46 0, 50, 8%
Pacific,1, 0, 0, 1, 0%
Africa, 0, 0, 19, 19, 100%
Total, 10, 56, 34, 100, 34%
  1. Add region as a dimension in visual
  2. Pivot the table by adding calculated fields for each of the categories-
  • Add calculated field a with expression ifelse({category} = 'a') then {value} else 0.
  • Add calculated field b with expression ifelse({category} = 'b') then {value} else 0.
  • Add calculated field c with expression ifelse({category} = 'c') then {value} else 0.
  1. Add a, b, and c to the visual individually. The default visual aggregation sum should work in this case. Verify this gives you expected values.
  2. Adding {value} directly in visual using default aggregation sum should give you the total.
  3. Add calculated field percent of total with expression sum({c}) / sum({value}) and add the field to the visual.

Please let me know if this works.

Thanks!

1 Like