Pivot calculated field based on data columns

[STGE2]

Hi All

I have been struggling with this for many hours now and cannot seem to find any help on this on google.

See below picture for the explanation of what I'm looking for:

In words I'm trying to get the percentage of count c / count b, so that when the user expands and collapse the rows the percentages shows as a total of that row (I tried to show this with a copy of the same pivot table).

I have tried to solve this with calculated fields but to no success

I do not want to edit my datasource as it is imported data from an external data link.

I do not want to write a macro which modified the imported data if it can be helped.

I do not want to manually add another column besides the pivot table and create a macro to dynamically add the formulas based on pivot length (have done this on several occasions and it does not seem like a very nice solution).

Hope one of you guys have more pivot knowledge than me and can answer :D

Thanks in advance!

Best regards
Stine

 

[Andrew Poulsom]

You can't do it with a calculated field in the pivot table because it will use Sum rather than Count. You can add a formula column to your source data:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	a	b	c	Percent		a	b	Count of c	Count of b	Average of Percent
2	mm	1f	x	100%		hh		3	5	60%
3	mm	1g		0%		mm		1	3	33%
4	mm	1h		0%		rr	1f		1	0%
5	hh	1g	x	100%			1g		1	0%
6	hh	1h		0%			1h		1	0%
7	hh	1j		0%		Grand Total		4	11	36%
8	hh	1k	x	100%
9	hh	1e	x	100%
10	rr	1f		0%
11	rr	1g		0%
12	rr	1h		0%
Sheet2

Cell Formulas
Range	Formula
D2	=COUNTIFS(A$2:A$12,A2,B$2:B$12,B2,C$2:C$12,"x")/COUNTIFS(A$2:A$12,A2,B$2:B$12,B2)

 

[RoryA]

If you have PowerPivot you can do it easily because you can create Count measures and use those. If not, perhaps you can edit the link to the external data to include the additional columns you need.

 

[Fazza]

hello Stine

To reference the source data you can have either a normal (non-dynamic) named range, say YourData, or use a worksheet name with leading "[" and trailing "$]" like [sheetname$]

I'll describe a manual approach, but you can automate it if you want.

Save the data file. From a new file ALT-D-P and see the wizard. Choose external data source at the first step. Get data, excel files, etc, etc. Keep going to the last step then choose the option to edit in MS Query. Via the SQL button replace the text you see by,

SELECT A.a, A.b, A.c, B.d
FROM YourTable A, 
(SELECT a,b, COUNT(c)/COUNT(b) AS d
FROM YourTable
GROUP BY a, b) B
WHERE A.a = B.a AND A.b = B.b

OK to enter this, OK to acknowledge any message you see. Then via the 'open door' icon exit MS Query and then 'Finish' to make the pivot table. For field d choose average (not default SUM).

If you want you can move the whole resultant worksheet into the source data file so that the pivot table is in the same file as the source data.

regards

 

[STGE2]

Thank you very much for all your answers! They were all usefull to me.

My first approach to solve this issue will now be to see if I can get the datascource query updated (I think this will be the most elegant solution).

Solution B will be to write a macro which automatically add another column to the data import table with the relevant function after the import has taken place.

Finally I will look a little into this PowerPivot stuff as I do not know about this

Best regards
Stine

 

[Fazza]

And what of my proposal? It does what you wanted - no change to data source, no macro, no extra columns - and now isn't even 'plan C'.

I'm genuinely curious as such solutions are often ignored & I don't know why. Is there some element of the explanation that is unclear? Does it not do what you want?

regards

 

[STGE2]

Hi Frazza

Thanks for writing again! You are absolutly right I missed the essence of what you wrote and thourght you were trying to apply the calculation to the actual query.

Best regards
Stine

 

[Fazza]

OK, Stine. Thank you. My communication (and maybe more!) needs to improve.

Specific implementation of the approach may need review/consideration, btw.