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!
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!