Pivot Transform Enhancement Percent of Column

Feature request
1

Today the Pivot Transform has many great options. See below.
How about adding Percent of Column total.
It can be calculated as the pivot row value divided by the sum of column

Pivot

Description
Creates a pivot table to summarise values for one or two columns.

Inputs
One.

Sum show the sum of the values. Non-numeric and empty values are ignored.
Minimum shows the smallest value. Non-numeric and empty values are ignored.
Maximum shows the largest value. Non-numeric and empty values are ignored.
Average shows the arithmetic mean of the values. Non-numeric and empty values are ignored.
Median shows the median of numeric values in the column. Non-numeric and empty values are ignored.
Mode shows the mode of numeric values in the column. Non-numeric and empty values are ignored.
Standard deviation is the sample standard deviation (equivalent to Excel function stddev.s). Non-numeric and empty values are ignored.
Count shows the number of values. Empty values are ignored (a value that contains whitespace is not considered empty).

1 Like
2

Dan

I have thought about this a bit. It seems there are a lot of ways this could be done. Do you mean to add extra % columns/rows. e.g. the red figures below:

percentage

Or instead of the existing totals?

Or do you mean to alter the existing values in the table?

Perhaps you could provide an image of what it looks like now and what you would like it to look like.

Another alternative is to have a new ‘range’ transform that let you modify all the values in a table into a range (default 0 to 100) based on either the minimum/maximum/total value in each column, row or table.

3

Excel has quite a few options.

excel
excel723×653 177 KB

4

Hi, my original thought was to select the % of Total as a single column.

Row Labels Sum of Disputed Dollars
Billing Accuracy 48.98%
Terms & Conditions 20.44%
Unknown 13.54%
Delivery & Installation 12.95%
Service Delivery 2.83%
Technical Issues 0.64%
Cash Applications 0.38%
Service/Supplies Credit Memo 0.23%
Payment Options 0.01%

Yet, showing both the Count and Percent of Column total could be very helpful.
Such as;

Row Labels Sum of Disputed Dollars2 Sum of Disputed Dollars
Billing Accuracy $4,128,367.37 48.98%
Terms & Conditions $1,722,366.90 20.44%
Unknown $1,141,031.66 13.54%
Delivery & Installation $1,091,424.82 12.95%
Service Delivery $238,914.81 2.83%
Technical Issues $53,816.33 0.64%
Cash Applications $32,271.00 0.38%
Service/Supplies Credit Memo $19,219.20 0.23%
Payment Options $469.12 0.01%
5

This gets more tricky if you are pivoting on 2 values.

I am still thinking that this might be more flexibly done as a ‘range’ transform. In the above case, do the Pivot then add a Range transform to convert the values into the range 0 to 100 based on the sum of the column. It is an extra transform, but it means you can convert anything to a percentage, not just a pivot table.

6

@DanFeliciano @Nicolai

I have been experimenting with a new Scale transform. It allows you to scale 1 or more columns:

  • by the Sum or Maximum
  • in all values, for each column or for each row

For example

Scale a single column to a percentage:

scale-example-1
scale-example-1428×1037 169 KB

Scale a table with row, column and grand totals to a percentage:

scale-example-2
scale-example-2722×1092 202 KB

You can use Num Format to tweak the decimal places and Insert to add a % symbol afterwards.

It is a bit more complicated than just calculating percentages based on sums, but a lot more flexible. For example you can scale each row so the maximum value in each row is 1.0.

What do you think? Any suggestions for a better name than Scale?

7

I like the functionality of the Transform and, as you said, it’s more robust than what I recommended.

Sounds, as if you’re struggling with the name of the Transform, and so am I.

Perhaps;
Scale, Divvy, Normalize, Equate, Distribute, Allocate, Apportion, Partition, Appropriate, Allot, Segment…

8

I think I prefer ‘scale’ out of those.
BTW “Divvy” = stupid in some parts of the UK. ;0)

9

I agree with you, Scale seems to be the best name… so far.

10

You can try the new Scale transform here:

https://www.easydatatransform.com/easydatatransform_v1i1.html

Let me know what you think.