In a pivot table, can I adjust the logic for calculating subtotal averages?

[jjjason10]

Good Afternoon All,

I've spent the last few hours searching for this issue with no luck, so I apologize if this has been posted already.

If I add a subtotal to a pivot table and make the subtotal calculate as "Average", I'm wondering if there is a way to adjust how excel calculates the average. Below is a snippet of sample data, similar to my pivot table. I have a few sales teams where I show each individuals total, then there is a roll up into the team total. I also have it set up so that I can see the team average. However my data set feeding the table has a line of data for each day of the sales cycle. As a reference, TEAM A has 383 lines of data in the date set. It appears that when I add in the subtotal Average, excel is calculating the average based on the number of lines of data in the data set and not the number of employees on the team. The numbers in Red font to the right of the table are my expected resultes (Taking the sum divided by 6). Is there anyway I can acheive this with in the table, and not use formulas in external columns.

Book1
	A	B	C	D	E	F	G
3	Row Labels	Sum of Leads	Sum of Contacted	Sum of Sale
4	North	1062.00	678.00	265.00
5	TEAM A
6	Employee 1	77.00	58.00	23.00
7	Employee 2	96.00	90.00	35.00
8	Employee 3	83.00	73.00	21.00
9	Employee 4	124.00	73.00	39.00
10	Employee 5	96.00	81.00	21.00
11	Employee 6	81.00	51.00	18.00
12	TEAM A Sum	557.00	426.00	157.00
13	TEAM A Average	1.45	1.11	0.41	93	71	26
14	TEAM B
15	Employee 1	175.00	79.00	38.00
16	Employee 2	93.00	58.00	32.00
17	Employee 3	91.00	48.00	18.00
18	Employee 4	87.00	49.00	19.00
19	Employee 5	59.00	17.00	1.00
20	Employee 6	0.00	1.00	0.00
21	TEAM B Sum	505.00	252.00	108.00
22	TEAM B Average	1.84	0.92	0.39	84	42	18
23	Grand Total	1062.00	678.00	265.00
Sheet3

Cell Formulas
Range	Formula
E13	=B12/6
E22	=B21/6
F13	=C12/6
F22	=C21/6
G13	=D12/6
G22	=D21/6

 

[oldbrewer]

TEAM A	TEAM A	leads	contacted	sales
TEAM A	Employee 1	77	58	23					TEAM A Sum	557	426	157
TEAM A	Employee 2	96	90	35					TEAM A Average	1.45	1.11	0.41
TEAM A	Employee 3	83	73	21
TEAM A	Employee 4	124	73	39
TEAM A	Employee 5	96	81	21					TEAM B Sum	505	252	108
TEAM A	Employee 6	81	51	18					TEAM B Average	1.84	0.92	0.39
									Grand Total	1062	678	265
TEAM B	TEAM B
TEAM B	Employee 1	175	79	38
TEAM B	Employee 2	93	58	32
TEAM B	Employee 3	91	48	18
TEAM B	Employee 4	87	49	19
TEAM B	Employee 5	59	17	1
TEAM B	Employee 6	0	1	0
TEAM B	Employee 7	33	44	55
	note team B has 7 employees
							leads	contacted	sales
						TEAM A	557	426	157
						TEAM B	505	252	108
							emps
						TEAM A	6
						TEAM B	7
							averages
							leads	contacted	sales
						TEAM A	92.83333	71	26.16666667
						TEAM B	84.16667	42	18
					these tables all produced using sumproduct

<colgroup><col><col><col span="7"><col><col span="4"></colgroup><tbody>
</tbody>

 

[jjjason10]

Thanks for the response oldbrewer. I'm not 100% sure I follow though, are you suggesting to use sumproduct in the pivot table? If so, how would I do that?

 

[oldbrewer]

wasnt sure if your data is pivot table output - if so it looks like raw data. My output is by processing raw data

 

[jjjason10]

Ahhh, yeah. The output is pivot data, my bad. Without the ability to attach a file, I wasn't completely sure how to provide a viewable example. I used the mr.excel add-in.

 

[Fazza]

The dataset can be modified - summed by employee - between the source & the pivot table, using SQL. SQL detail will be specific to your data - so without knowing that I can't advise.

To set it up, approach is save the data file, then from a new file (CTRL-N) keystrokes ALT-D-N to start the pivot table wizard, then choose external data source at the first step, next, get data, browse for your file. If you haven't got a simple (non-dynamic) named range for the source data there might be a message about no visible tables - just OK to that and then via 'options' choose 'system tables' to see worksheet names - choose the name of the named range or worksheet with the data. Continue to the end & then choose the MS Query option. Within MS Query modify the SQL, see the modified dataset, then exit MS Query & make a pivot table. The entire worksheet containing the pivot table can be moved to the source data workbook if you like.

regards, Fazza

 

[oldbrewer]

In this case I think analysis of raw data without using pivot tables may be best. Show us 20 lines of raw data - anonymised if necessary.

 

[jjjason10]

Thanks for the responses oldbrewer and Fazza. The data tends to be pretty dynamic with reps changing teams frequently, so not using pivot tables might require a lot of manual up keep. I didn't really think this was an easy/doable solution, so I think i'm going to move forward with my plan B. I'll build a 2nd query that sums all the data at the rep level then build a 2nd tab to display averages. I think that will be as effective as building out the SQL piece.

Thanks again for the ideas.

 

[oldbrewer]

team	name	leads	contacted	sales
TEAM B	Employee 1	175	79	38
TEAM A	Employee 3	83	73	21			in this example people switch teams
TEAM B	Employee 3	91	48	18
TEAM A	Employee 5	96	81	21
TEAM A	Employee 2	96	90	35				leads	contacted	sales
TEAM B	Employee 4	59	17	1			TEAM A	557	426	157
TEAM A	Employee 4	81	51	18			TEAM B	451	247	144
TEAM A	Employee 1	77	58	23
TEAM B	Employee 6	33	44	55				1008	673	301	check
TEAM A	Employee 6	124	73	39
TEAM B	Employee 5	0	1	0
TEAM B	Employee 2	93	58	32
		1008	673	301	check

<colgroup><col><col><col span="12"></colgroup><tbody>
</tbody>

 

[Fazza]

I'll build a 2nd query that sums all the data at the rep level then build a 2nd tab to display averages. I think that will be as effective as building out the SQL piece.

Yes, a similar approach though with more transparency.

I forgot yesterday that the GUI in MS Query will enable what is wanted without needing to manually edit the SQL. You can just delete the day field and then select the value field to be summed and press the upper case sigma icon. The 'plan B' query you'll be creating will be identical, I think, and just be an extra step (though with the benefit of transparency, and a small cost in memory and the extra step en route to the result).