Pivot Table - Data Field - Opposite of Running Total In

[rg1117]

Hi All,

I trying to create a custom data field within a Pivot Table that
calculates the remaining sum, in a sense the opposite of the "Running
Total In" option fo the "Show data as" parameter in the Field Settings
bar. So for example if I have the follow data items in a column:

1, 4, 7, 3, 7
Column Total being 22

Then the running total would be
1, 5, 12, 15, 22

And the remaining sum would be
21, 17, 10, 18, 0

I cannot find any way of doing this. Since I cannot use GETPIVOTDATA
within a Pivot Table's calculated field, I cannot make a reference to
the Column Total. Can anyone suggest a way of doing this using.

Ideally if possible, I want to avoid using macros for this as the
source data can change quite significantly, a non-macro based solution
would be optimal for my context.

Any suggestions would be greatly appreciated.

Thanks for your help in advance,

RG

 

[Herbert Seidenberg]

Assuming your data at $A$1 looks like this:
seq run_tot rem_tot
a 1 21
b 4 17
c 7 10
d 3 7
e 7 0

Select the array and
Insert > Name > Create > Top Row
Clear the content of column rem_tot for now.
Create a pivot table at $A$9 with run_tot (with Running Total in)
and rem_tot (Sum) in the data field.
The table's format (ignore numbers for now) should look like this:
seq Data Total
a Sum of run_tot 1
Sum of rem_tot 21
b Sum of run_tot 5
Sum of rem_tot 17
c Sum of run_tot 12
Sum of rem_tot 10
d Sum of run_tot 15
Sum of rem_tot 7
e Sum of run_tot 22
Sum of rem_tot 0

Enter this formula into rem_tot of the first array and copy down:
=GETPIVOTDATA("Sum of run_tot",$A$9,"seq","e")-
GETPIVOTDATA("Sum of run_tot",$A$9,"seq",seq 2:2)
and refresh the pivot table.

 

[rg1117]

Hi There,

Thank you very much for your reply, it's very much appreciated.

I am trying to follow your suggestions but am not able to do it. I
think it may not be appropriate for my situation. I should have given a
better description of the problem in the first place, My Bad! So I will
try to explain it, properly this time.

Suppose my raw data looks something like this:
There are two variables X, Y each with four possible values xa, xb, xc,
xd & ya. yb. yc, yd

X Y
xa ya
xa yb
xa yc
xa yd
xb ya
xb yb
xb yc
xb yd
xc ya
xc yb
xc yc
xc yd
xd ya
xd yb
xd yc
xd yd
. .
etc


I create a Pivot Table to count the combinations of each X value with
each Y value:

Count of X X
Y xa xb xc xd Grand Total
ya 2 2 2 2 8
yb 2 2 3 1 8
yc 1 2 2 2 7
yd 1 2 2 2 7
Grand Total 6 8 9 7 30

Thus Column field is X, Row field is Y, and for the Data field I can
actually add either X or Y, though I have added X. The Data field is
Count of X

Next I want to add a Running Total of the Count so I add another
instance of X as a Data field and change "Field Settings" > Options >
"Show data as:" > "Running Total In" > Y. I get the Pivot Table:

X
Y Data xa xb xc xd Grand Total
ya Count of X 2 2 2 2 8
Running Total of X 2 2 2 2 8
yb Count of X 2 2 3 1 8
Running Total of X 4 4 5 3 16
yc Count of X 1 2 2 2 7
Running Total of X 5 6 7 5 23
yd Count of X 1 2 2 2 7
Running Total of X 6 8 9 7 30
Total Count of X 6 8 9 7 30
Total Running Total of X

To this, I want to add another Data field Remaining Sum of X (as I had
described in my original post) for each Column. To make it even more
complicated, I actually need this number as a percentage of the total.
So the table with Remaining Sum would look like:

Y Data xa xb xc xd Grand Total
ya Count of X 2 2 2 2 8
Running Total of X 2 2 2 2 8
Remaining Sum 4 6 7 5 22
yb Count of X 2 2 3 1 8
Running Total of X 4 4 5 3 16
Remaining Sum 2 4 4 4 14
yc Count of X 1 2 2 2 7
Running Total of X 5 6 7 5 23
Remaining Sum 1 2 2 2 7
yd Count of X 1 2 2 2 7
Running Total of X 6 8 9 7 30
Remaining Sum 0 0 0 0 0
Total Count of X 6 8 9 7 30
Total Running Total of X
Total Remaining Sum 6 8 9 7 30

So my question basically is about how to calculate the "Remaining Sum"
part of the pivot table, which as far as I can tell is not possible
with the standard options for the Pivot Tables. The simplest solution
would to have an equation like "Total Count of X" - "Count of X" (for
that particular Column/Row), but unfortunately it is not possible to
make references to the same Pivot Table from a Calculated Field
Formula.

I hope I did a better job of explaining it then the first time round!
Any help would be appreciated.

Many Thanks,

RG

 

[Herbert Seidenberg]

Maybe a formula approach is acceptable.
Name your data like this:
Ym Xm
ya xa
ya xa
yb xa
yb xa
yc xa
yd xa
ya xb
.... ...

Create two helper arrays like this:
xa xb xc xd Xs
ya 2 2 2 2
yb 2 2 3 1
yc 1 2 2 2
yd 1 2 2 2
Yn

ya 2 2 2 2
yb 4 4 5 3
yc 5 6 7 5
yd 6 8 9 7
Ys

Name the headers Xs, Yn and Ys
Select xa, xb, xc, xd and check the name Xs in the name window.
Select xa..xd and the numbers in first array (20 cells) and
Insert > Name > Create > Top Row
Name the numbers in the second array (16 cells) array2
Enter this formula (R1C1 Style) into the first array:
=SUMPRODUCT((Ym=Yn R)*(Xm=Xs C))
Enter this formula into the second array:
=IF(Ys="ya",SUMPRODUCT((Ym=Ys R)*(Xm=Xs C)),
SUMPRODUCT((Ym=Ys R)*(Xm=Xs C))+R[-1]C)
Enter this array formula (Cntl+Shift+Enter) into an array
aligned with the arrays above:
=1-array2/SUM(INDIRECT(Xs))
When formatted as percentage the results are
67% 75% 78% 71%
33% 50% 44% 57%
17% 25% 22% 29%
0% 0% 0% 0%

 

[rg1117]

Hi there,

Just wanted to say thanks for your help on this. I could not use the
example directly but your explanation gave me ideas about how to get
the solution.

Thanks,

RG