Simple Calculation using PIVOT

[powerwill]

Hi Experts!

I have a simple problem, that I am looking to resolve. I am trying to get the Total % of Agents Meeting the Target which I haven't been able to figure out using the Pivot itself, and hence I am using a formula besides the Pivot so I can get the total percentage. (See the Yellow Columns/cells highlighted in the linked Excel Sheet)

Unlike the Total % of Audits meeting the target, I cannot seem to find a way around without using a separate formula. Can this be calculated within the same pivot, perhaps using a calculated field?

Link: Excel Sheet

Any help is appreciated!!

 

[powerwill]

Apologies if I wasn't clear earlier.

I need to configure my Pivot to have a field that shows 1 or 100% for agents whose average score is greater than 92% and a 0 or 0% for people with average scores below 92%, so we can eliminate any functions or manual calculation in order to get the total % of Agents meeting the Target... All of this with only using Columns A, B, & C as the source data, is that possible? I use Office365. I need it in the pivot because its linked to an interactive Dashboard.

@SpillerBD would really appreciate your thoughts on this. Youll find the Excel sheet in the OP post. Any help is appreciated! ??

 

[powerwill]

Apologies if I wasn't clear earlier.

I need to configure my Pivot to have a field that shows 1 or 100% for agents whose average score is greater than 92% and a 0 or 0% for average scores below 92%, so we can eliminate any functions or manual calculation in order to get the total % of Agents meeting the Target... All of this with only using Columns A, B, & C as the source data, is that possible? I use Office365. I need it in the pivot because its linked to an interactive Dashboard.

@BSALV any suggestion is appreciated!

 

[BSALV]

the calculations have to be done in the table, there the agent gets a 0 or 1 depending on his average, so you have to add an auxiliary column.

mrexcel (1).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Agent	Audit Score	If Target Met	average if…					Rijlabels	Average of If Target Met	Target meet		Target Met
2	Abdul	96%	1	1					Abdul	100,00%	1,00		1
3	Abdul	97%	1	1		TARGET	92%		Ahmed	81,82%	0,00		0
4	Abdul	98%	1	1					Alfred	54,55%	0,00		0
5	Abdul	100%	1	1					Eugene	54,55%	0,00		0
6	Abdul	100%	1	1					Krishna	72,73%	0,00		0
7	Abdul	100%	1	1					Liza	54,55%	0,00		0
8	Abdul	100%	1	1					Mary	54,55%	0,00		0
9	Abdul	100%	1	1					Monica	54,55%	0,00		0
10	Abdul	100%	1	1					Paul	27,27%	0,00		0
11	Abdul	100%	1	1					Peter	54,55%	0,00		0
12	Abdul	100%	1	1					Ralph	100,00%	1,00		1
13	Ahmed	56%	0	0					Simon	100,00%	1,00		1
14	Ahmed	87%	0	0					Sonya	100,00%	1,00		1
15	Ahmed	92%	1	0					Suzy	100,00%	1,00		1
16	Ahmed	99%	1	0					Eindtotaal	72,08%	0,36		35,71%
17	Ahmed	100%	1	0
18	Ahmed	100%	1	0
19	Ahmed	100%	1	0		Total % of Agents Meeting Target			35,71%
20	Ahmed	100%	1	0		Total % of Audits Meeting Target			72,08%
21	Ahmed	100%	1	0		Total Average of Score			#VERW!
22	Ahmed	100%	1	0
Sheet1

Cell Formulas
Range	Formula
C2:C22	C2	=IF(B2>$G$3,1,0)
D2:D22	D2	=--(AVERAGEIF([Agent],[@Agent],[If Target Met])>=$G$3)
M2:M15	M2	=IF(J2>$G$3,1,0)
M16	M16	=AVERAGE(M2:M15)
I19	I19	=+GETPIVOTDATA("Target meet",$I$1)
I20	I20	=GETPIVOTDATA("Average of If Target Met",$I$1)
I21	I21	=GETPIVOTDATA("Average of Score",$I$1)

 

[powerwill]

the calculations have to be done in the table, there the agent gets a 0 or 1 depending on his average, so you have to add an auxiliary column.

mrexcel (1).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Agent	Audit Score	If Target Met	average if…					Rijlabels	Average of If Target Met	Target meet		Target Met
2	Abdul	96%	1	1					Abdul	100,00%	1,00		1
3	Abdul	97%	1	1		TARGET	92%		Ahmed	81,82%	0,00		0
4	Abdul	98%	1	1					Alfred	54,55%	0,00		0
5	Abdul	100%	1	1					Eugene	54,55%	0,00		0
6	Abdul	100%	1	1					Krishna	72,73%	0,00		0
7	Abdul	100%	1	1					Liza	54,55%	0,00		0
8	Abdul	100%	1	1					Mary	54,55%	0,00		0
9	Abdul	100%	1	1					Monica	54,55%	0,00		0
10	Abdul	100%	1	1					Paul	27,27%	0,00		0
11	Abdul	100%	1	1					Peter	54,55%	0,00		0
12	Abdul	100%	1	1					Ralph	100,00%	1,00		1
13	Ahmed	56%	0	0					Simon	100,00%	1,00		1
14	Ahmed	87%	0	0					Sonya	100,00%	1,00		1
15	Ahmed	92%	1	0					Suzy	100,00%	1,00		1
16	Ahmed	99%	1	0					Eindtotaal	72,08%	0,36		35,71%
17	Ahmed	100%	1	0
18	Ahmed	100%	1	0
19	Ahmed	100%	1	0		Total % of Agents Meeting Target			35,71%
20	Ahmed	100%	1	0		Total % of Audits Meeting Target			72,08%
21	Ahmed	100%	1	0		Total Average of Score			#VERW!
22	Ahmed	100%	1	0
Sheet1

Cell Formulas
Range	Formula
C2:C22	C2	=IF(B2>$G$3,1,0)
D2:D22	D2	=--(AVERAGEIF([Agent],[@Agent],[If Target Met])>=$G$3)
M2:M15	M2	=IF(J2>$G$3,1,0)
M16	M16	=AVERAGE(M2:M15)
I19	I19	=+GETPIVOTDATA("Target meet",$I$1)
I20	I20	=GETPIVOTDATA("Average of If Target Met",$I$1)
I21	I21	=GETPIVOTDATA("Average of Score",$I$1)

Perfect!!! Exactly what I needed. Thank you @BSALV for your kind attention and time. Also, thank you @GlennUK for your initial guidance.