Excel - running % of running total in pivot table

Question

I have a table like:

periodo quintil pos
201611  1   10
201611  2   20
201611  3   30
201611  4   40
201611  5   50
201612  1   9
201612  2   19
201612  3   29
201612  4   39
201612  5   49

I need to create a pivot table like:

periodo quintil running_pos running_%
201611  
    1   10  7%
    2   30  20%
    3   60  40%
    4   100 67%
    5   150 100%
201612  
    1   9   6%
    2   28  19%
    3   57  39%
    4   96  66%
    5   145 100%

Since the running total is not a new field, but a way to show an older field (pos- show as total in quintil), the problem arises when I try to create the running % of the running total.

How can I introduce also this field (running % of running total)?

In spanish there's nothing with a name like running totals translation....

you can try some of the Show Values As http://www.thecompanyrocks.com/how-to-use-the-show-as-values-dialog-box-for-an-excel-pivot-table/ — Slai, Dec 15 '16 at 13:36
If I just select to `% show running total` in `quintil`, I get what you display. What is the problem. — Ron Rosenfeld, Dec 15 '16 at 13:38
@RonRosenfeld, have you tried to do it? As I wrote, the running_pos is not a field that I can use (it's only a way to show pos field). — GabyLP, Dec 15 '16 at 13:42
@GabyLP Yes, I did. And I get what you show above as the result. Just as I wrote in my comment. — Ron Rosenfeld, Dec 15 '16 at 13:44
@RonRosenfeld, I don't have the options of showing a field that doesn't exist. What you are saying returns the wrong numbers. Please verify them. — GabyLP, Dec 15 '16 at 13:46
maybe I misunderstood the problem, but you can use the same field `pos` more than once in the Values Fields — Slai, Dec 15 '16 at 13:46

score 1 · Answer 1 · answered Dec 15 '16 at 13:49

1

To display what you want in a Pivot Table - Drag pos to the values area three times - For the first, use the SUM - For the second, use the "show as running total" - For the third, use the "show as % running total"

Here are the results with minimal formatting

Here are the value settings for the third column:

answered Dec 15 '16 at 13:49

Ron Rosenfeld

53,870
7
28
60

In Spanish it's completely different.... There's nothing like running total. That was the issue. You have to select, "mostrar valores como %", "campo base" =quintil, "elemento base"=5 – GabyLP Dec 15 '16 at 14:00
@GabyLP Are you still using the SUM function in the `Summarize Values By` tab? – Ron Rosenfeld Dec 15 '16 at 14:05
yes, but still I get the wrong result, I don't have anything like running total – GabyLP Dec 15 '16 at 14:25
@GabyLP What version of Excel are you using? – Ron Rosenfeld Dec 15 '16 at 14:26
2010 version , I tried % del total en quintil and I got the % of the max value, I tried in 2013 and I got the correct result (same selection). – GabyLP Dec 15 '16 at 14:31
@GabyLP So can you just use 2013? – Ron Rosenfeld Dec 15 '16 at 14:32

Excel - running % of running total in pivot table

1 Answers1