Pivot table subtotals showing average, how to get that into a percentage?

supernova29

New Member
Joined
Nov 22, 2018
Messages
1
Hello all,

I'm completely new to pivots in general, but it seems like my expectations are already leading me to look for something more advanced than my knowledge would allow.

My source of data is a list of products and their weekly rankings.
My pivot table shows product's ranking on weekly basis, as well as the improvement from the initial ranking, both as a difference (amount of positions climbed in the ranking) and as a percentage over the initial ranking. Very basic maths.

Products are divided into categories, and marked with one among 4 possible "flags".
The purpose of the pivot is that it automatically shows an average for each category and flag, as well as in grand totals: average initial ranking, average current ranking, average improvement (difference), average improvement (%); they change as I expand/collapse fields or use slicers.

Obviously, the subtotals and grand total of the percentage are an average of the individual percentages I have in the table, one for each product.
However, for the purpose of what I'm doing, I would need those to actually calculate a (new) percentage directly on the average (subtotal) rankings.
This makes much difference: two products may both have climbed 100 positions in the ranking, but maybe those 100 positions mean 30% improvement for one and 3% improvement for the other.
In other words, I need to find a way to translate subtotals and grand total into a percentage, and display that.

Any advice?

Thanks a lot in advance!
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Not sure I understand, but would right clicking the amount and using Show values as - % of grand total or % of parent total work for you?
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top