Percentage of Grand Total in Row

[TH123]

Hi,

I have data like below and I create pivot table accordingly. I want to add percentage as row directly as row#8 which I manually calculate in pivot table with logic: each column data multiple Grand total in column.
Thank you.



Data source

Book1
	A	B	C	D
1	Issue	Item	Quantity	Days Diff
2	Late added	A	1000	0
3	Late added	B	500	7
4	Late added	C	200	14
5	Fab issue	D	400	21
Sheet1

PIvot table

Book1
	H	I	J	K	L	M	N
1	Sum of Quantity		Days Diff
2	Issue	Item	0	7	14	21	Grand Total
3	Fab issue	D				400	400
4	Late added	A	1000				1000
5		B		500			500
6		C			200		200
7	Grand Total		1000	500	200	400	2100
8	%		48%	24%	10%	19%	100%
Sheet1

Cell Formulas
Range	Formula
J8:N8	J8	=J7/$N$7

 

[RoryA]

You'd have to add the Quantity field again and show it as % of row total, then either hide the additional rows within the pivot table, or load it to the data model and create a row set to only show it at the Grand total level.

 

[TH123]

You'd have to add the Quantity field again and show it as % of row total, then either hide the additional rows within the pivot table, or load it to the data model and create a row set to only show it at the Grand total level.

Hi RoryA,

Thanks for sharing comments. Yes, I've tried before but new adding is column as chart below, not row as my 1st chart. What I want is adding new row to show % of each column vs G.Total.

Book1
	H	I	J	K	L	M	N	O	P	Q	R	S
1			Values	Days Diff
2			Sum of Quantity				Sum of Quantity2				Total Sum of Quantity	Total Sum of Quantity2
3	Issue	Item	0	7	14	21	0	7	14	21
4	Fab issue	D				400	0.00%	0.00%	0.00%	100.00%	400	100.00%
5	Late added	A	1000				100.00%	0.00%	0.00%	0.00%	1000	100.00%
6		B		500			0.00%	100.00%	0.00%	0.00%	500	100.00%
7		C			200		0.00%	0.00%	100.00%	0.00%	200	100.00%
8	Grand Total		1000	500	200	400	47.62%	23.81%	9.52%	19.05%	2100	100.00%
Sheet1

 

[Peter_SSs]

Formatting is not as good, but if you have the PIVOTBY function, could you use a formula approach something like this?

25 01 07.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Issue	Item	Quantity	Days Diff						0	7	14	21	Total
2	Late added	A	1000	0				Fab issue	D				400	400
3	Late added	B	500	7				Late added	A	1000				1000
4	Late added	C	200	14				Late added	B		500			500
5	Fab issue	D	400	21				Late added	C			200		200
6								Total		1000	500	200	400	2100
7								%		0.48	0.24	0.1	0.19	1
PT

Cell Formulas
Range	Formula
H1:N7	H1	=LET(d,A2:D5,q,INDEX(d,0,3),p,PIVOTBY(TAKE(d,,2),TAKE(d,,-1),q,SUM),VSTACK(p,MAKEARRAY(1,COLUMNS(p),LAMBDA(r,c,IF(c=1,"%",IF(c>2,ROUND(INDEX(p,ROWS(p),c)/SUM(q),2),""))))))
Dynamic array formulas.

If those last row percentages are not to be used in other calculations we could make them text so they look better.

25 01 07.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Issue	Item	Quantity	Days Diff						0	7	14	21	Total
2	Late added	A	1000	0				Fab issue	D				400	400
3	Late added	B	500	7				Late added	A	1000				1000
4	Late added	C	200	14				Late added	B		500			500
5	Fab issue	D	400	21				Late added	C			200		200
6								Total		1000	500	200	400	2100
7								%		48%	24%	10%	19%	100%
PT (2)

Cell Formulas
Range	Formula
H1:N7	H1	=LET(d,A2:D5,q,INDEX(d,0,3),p,PIVOTBY(TAKE(d,,2),TAKE(d,,-1),q,SUM),VSTACK(p,MAKEARRAY(1,COLUMNS(p),LAMBDA(r,c,IF(c=1,"%",IF(c>2,TEXT(INDEX(p,ROWS(p),c)/SUM(q),"0%"),""))))))
Dynamic array formulas.

 

[RoryA]

Hi RoryA,

Thanks for sharing comments. Yes, I've tried before but new adding is column as chart below, not row as my 1st chart. What I want is adding new row to show % of each column vs G.Total.

You need to move the Values field to the Row area, not the Column area in the field list:

 

[TH123]

You need to move the Values field to the Row area, not the Column area in the field list:

View attachment 121025

Appreciated yoru support . Finally I can archive what I want with your support. Could you please show me result of "load it to the data model and create a row set to only show it at the Grand total level"? I have load it to the data model in pivot table however don't know how to create a row set to only show it at grand total level. I still need to hide the row manually.

 

[TH123]

Formatting is not as good, but if you have the PIVOTBY function, could you use a formula approach something like this?

25 01 07.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Issue	Item	Quantity	Days Diff						0	7	14	21	Total
2	Late added	A	1000	0				Fab issue	D				400	400
3	Late added	B	500	7				Late added	A	1000				1000
4	Late added	C	200	14				Late added	B		500			500
5	Fab issue	D	400	21				Late added	C			200		200
6								Total		1000	500	200	400	2100
7								%		0.48	0.24	0.1	0.19	1
PT

Cell Formulas
Range	Formula
H1:N7	H1	=LET(d,A2:D5,q,INDEX(d,0,3),p,PIVOTBY(TAKE(d,,2),TAKE(d,,-1),q,SUM),VSTACK(p,MAKEARRAY(1,COLUMNS(p),LAMBDA(r,c,IF(c=1,"%",IF(c>2,ROUND(INDEX(p,ROWS(p),c)/SUM(q),2),""))))))
Dynamic array formulas.

If those last row percentages are not to be used in other calculations we could make them text so they look better.

25 01 07.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Issue	Item	Quantity	Days Diff						0	7	14	21	Total
2	Late added	A	1000	0				Fab issue	D				400	400
3	Late added	B	500	7				Late added	A	1000				1000
4	Late added	C	200	14				Late added	B		500			500
5	Fab issue	D	400	21				Late added	C			200		200
6								Total		1000	500	200	400	2100
7								%		48%	24%	10%	19%	100%
PT (2)

Cell Formulas
Range	Formula
H1:N7	H1	=LET(d,A2:D5,q,INDEX(d,0,3),p,PIVOTBY(TAKE(d,,2),TAKE(d,,-1),q,SUM),VSTACK(p,MAKEARRAY(1,COLUMNS(p),LAMBDA(r,c,IF(c=1,"%",IF(c>2,TEXT(INDEX(p,ROWS(p),c)/SUM(q),"0%"),""))))))
Dynamic array formulas.

Thank you for your attention on this matter. While this is a good way to address my expectation, I found another solution from RoryA that better fits my question.

 

[Peter_SSs]

Thank you for your attention on this matter. While this is a good way to address my expectation, I found another solution from RoryA that better fits my question.

You're welcome. No problem at all, glad you have been able to achieve what you want. Thanks for the follow-up though.

 

[RoryA]

Once you have the pivot table, you can create the row set:

Then in the next dialog, just delete the rows you don't want visible:

Note that if you need it to be dynamic (i.e. if the values in the row fields will change) then you will need to create a measure for the Quantity percent and also edit the MDX for that set, which is a little more complicated.

 

[TH123]

Once you have the pivot table, you can create the row set:
View attachment 121065

Then in the next dialog, just delete the rows you don't want visible:
View attachment 121066
Note that if you need it to be dynamic (i.e. if the values in the row fields will change) then you will need to create a measure for the Quantity percent and also edit the MDX for that set, which is a little more complicated.

Thanks for your kind explanation. I've tried to delete the row as same as your provided image however the result show no sum of quantity 2 in total. Could you please help to review and share comments?
FYI: I've tried to click into drop box at all the row with values equal to "Sum of quantity 2" => this value change to "Sum of quantity" automaticially.