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]

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!!

@GlennUK your expertise would help a lot!

 

[GlennUK]

You need to decide what the rules of calculation are. How are you assessing an agent? An agent can have scores above 92%, but the number of scores calculates as a different percentage. Are you wanting to score an agent on the average of all Audit Scores, or the Count of scores above 92% as a ratio of all audits per agent?

mrexcel.xlsx
	Q	R	S
1	Agent	Audit Score	If Target Met
2	Bill	56%	0
3	Bill	87%	0
4	Bill	92%	1
5	Bill	99%	1
6	average	84%	50%
Sheet1

 

[powerwill]

You need to decide what the rules of calculation are. How are you assessing an agent? An agent can have scores above 92%, but the number of scores calculates as a different percentage. Are you wanting to score an agent on the average of all Audit Scores, or the Count of scores above 92% as a ratio of all audits per agent?

mrexcel.xlsx
	Q	R	S
1	Agent	Audit Score	If Target Met
2	Bill	56%	0
3	Bill	87%	0
4	Bill	92%	1
5	Bill	99%	1
6	average	84%	50%
Sheet1

Thank you vmuch for your response.

Here I'm trying to find the total % of Agents whose average score is Greater than the given target of 92%.

Right now to do that I'm using the IF function and I'm getting the numbers right (Target met Yellow Column). However, I want to avoid using the function as the data keeps updating.

 

[GlennUK]

Is this acceptable?

mrexcel.xlsx
	A	B	C	D	E	F
1	Agent	Audit Score	If Target Met
2	Abdul	96%	1
3	Abdul	97%	1		TARGET	92%
4	Abdul	98%	1		Agent	Met?
5	Abdul	100%	1		Sonya	TRUE
6	Abdul	100%	1		Suzy	TRUE
7	Abdul	100%	1		Abdul	TRUE
8	Abdul	100%	1		Simon	TRUE
9	Abdul	100%	1		Ralph	TRUE
10	Abdul	100%	1		Mary	TRUE
11	Abdul	100%	1		Peter	TRUE
12	Abdul	100%	1		Ahmed	TRUE
13	Ahmed	56%	0		Liza	TRUE
14	Ahmed	87%	0		Krishna	TRUE
15	Ahmed	92%	1		Eugene	FALSE
16	Ahmed	99%	1		Alfred	FALSE
17	Ahmed	100%	1		Monica	FALSE
18	Ahmed	100%	1		Paul	FALSE
19	Ahmed	100%	1		OVERALL	71.43%
Sheet1

Cell Formulas
Range	Formula
C2:C19	C2	=IF(B2>$F$3,1,0)
F5:F18	F5	=AVERAGEIF(Table1[Agent],E5,Table1[Audit Score])>92%
F19	F19	=COUNTIF(F5:F18,TRUE)/COUNTA(F5:F18)

 

[powerwill]

Is this acceptable?

mrexcel.xlsx
	A	B	C	D	E	F
1	Agent	Audit Score	If Target Met
2	Abdul	96%	1
3	Abdul	97%	1		TARGET	92%
4	Abdul	98%	1		Agent	Met?
5	Abdul	100%	1		Sonya	TRUE
6	Abdul	100%	1		Suzy	TRUE
7	Abdul	100%	1		Abdul	TRUE
8	Abdul	100%	1		Simon	TRUE
9	Abdul	100%	1		Ralph	TRUE
10	Abdul	100%	1		Mary	TRUE
11	Abdul	100%	1		Peter	TRUE
12	Abdul	100%	1		Ahmed	TRUE
13	Ahmed	56%	0		Liza	TRUE
14	Ahmed	87%	0		Krishna	TRUE
15	Ahmed	92%	1		Eugene	FALSE
16	Ahmed	99%	1		Alfred	FALSE
17	Ahmed	100%	1		Monica	FALSE
18	Ahmed	100%	1		Paul	FALSE
19	Ahmed	100%	1		OVERALL	71.43%
Sheet1

Cell Formulas
Range	Formula
C2:C19	C2	=IF(B2>$F$3,1,0)
F5:F18	F5	=AVERAGEIF(Table1[Agent],E5,Table1[Audit Score])>92%
F19	F19	=COUNTIF(F5:F18,TRUE)/COUNTA(F5:F18)

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.

 

[GlennUK]

Explain why a non-PIvotTable solution is not usable to feed into an interactive dashboard?

 

[powerwill]

Explain why a non-PIvotTable solution is not usable to feed into an interactive dashboard?

I am told and I know Pivots are better and more efficient when working with Dashboards, besides the one who created the Dashboard has put close to 100 - 120 different tables that has made the file >100MB in size, so I am trying to figure out all possible ways to cut these tables/functions and see if I can make this work in the Pivot itself, now that we got office365. Tables would work sure, but do you think this is possible using a Pivot? Appreciate all your help ?

 

[GlennUK]

Why oh why, when it is obvious that it can be done easily using formulas (especially the new SPILL functions of Excel 365), do you still keep on wanting a PivotTable solution. Your "explanation" of why you need a PivotTable is not valid ("are better" for example). I give up - maybe someone else can chip it.

 

[powerwill]

Why oh why, when it is obvious that it can be done easily using formulas (especially the new SPILL functions of Excel 365), do you still keep on wanting a PivotTable solution. Your "explanation" of why you need a PivotTable is not valid ("are better" for example). I give up - maybe someone else can chip it.

No problem. Thanks for your attention.