% of row total of the running total in pivot table

[zuriqi]

Hi

Is it possible to do the following in pivot table?

1. Do running total
2. % of row total based on the values of the running total in point 1

[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

Running total

[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]6[/TD]
[TD]14[/TD]
[/TR]
</tbody>[/TABLE]

% of row total

[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]33%[/TD]
[TD]67%[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]44%[/TD]
[TD]56%[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]57%[/TD]
[TD]43%[/TD]
[TD]100%[/TD]
[/TR]
</tbody>[/TABLE]

so is there a way to achieve point 2 using calculated field or directly or any other way other than formula?

 

[sheetspread]

is there a way to achieve point 2 using calculated field or directly or any other way other than formula?

I don't think there's a running % of total by row pt setting, but if you're willing to bend the last requirement:

Excel 2010
	A	B	C	D	E	F	G	H
1	Name	Group	Number	running		Sum of running	Column Labels
2	E	A	1	1		Row Labels	A	B
3	E	B	2	2		E	33.33%	66.67%
4	Q	A	3	4		Q	44.44%	55.56%
5	Q	B	3	5		W	57.14%	42.86%
6	W	A	4	8
7	W	B	1	6
Sheet3

Cell Formulas
Range	Formula
D2	=SUMIF($B$2:$B2,B2,$C$2:$C$7)

 

[zuriqi]

I don't think there's a running % of total by row pt setting, but if you're willing to bend the last requirement:


Excel 2010

	A	B	C	D	E	F	G	H
Name	Group	Number	running	Sum of running	Column Labels
E	A	Row Labels	A	B
E	B	E
Q	A	Q
Q	B	W
W	A
W	B

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]33.33%[/TD]
[TD="align: right"]66.67%[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]44.44%[/TD]
[TD="align: right"]55.56%[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]57.14%[/TD]
[TD="align: right"]42.86%[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=SUMIF($B$2:$B2,B2,$C$2:$C$7)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for the reply , However, i know how to get this by formula , but i was wondering if this can be done by pivot table settings to avoid additional formulas and dynamic range setting, Thanks again for your reply

 

[sheetspread]

Power BI allows a source data dynamic formula or written query. VBA is another option.