Pivot Table

[faisus]

Hello people, hope all are doing good. I have a query in regards to pivot table. I have below table as data

Ratio is SP/GP.

When i do pivot, i require ratio to be shown as 80% for the whole Alpha group.

However the pivot shows me as this

I do not want the sum but to calculate as it is. SP/GP. Is there any way since i have big list divided into further groups. Thanks alot.

 

[Alex Blakenburg]

Mathematically you can't calculate an overall (group) ratio using the individual ratios it needs to be calculated based on the underlying values.
As such you need to create a calculated field that is = SP/GP then include that in your pivot and rename the heading which will still say Sum but isn't really a sum.
Calculated Fielld - PivotTable Analyze > Fields, Items & Sets > Calculated Field

20221222 Pivot Table Ratio faisus.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Group	Name	GP	SP	Ratio
2	Alpha	A	2,408,950	2,029,715	84%			Group	Calc Ratio
3	Alpha	B	1,932,774	1,455,799	75%			Alpha	80%
4	Beta	C	1,999,286	2,191,311	110%			Beta	105%
5	Beta	D	283,420	205,738	73%			Gamma	35%
6	Gamma	E	73,941	25,648	35%			Grand Total	88%
7
Sheet1

Cell Formulas
Range	Formula
E2:E6	E2	=D2/C2

 

[faisus]

Thank you works like a charm...

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.