Pivot calculated field based on data columns

STGE2

New Member
Joined
Oct 8, 2009
Messages
17
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:
540da131556eaf95936da9fd.jpg


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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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
ABCDEFGHIJ
1abcPercentabCount of cCount of bAverage of Percent
2mm1fx100%hh3560%
3mm1g0%mm1333%
4mm1h0%rr1f10%
5hh1gx100%1g10%
6hh1h0%1h10%
7hh1j0%Grand Total41136%
8hh1kx100%
9hh1ex100%
10rr1f0%
11rr1g0%
12rr1h0%
Sheet2
Cell Formulas
RangeFormula
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)
 
Upvote 0
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.
 
Upvote 0
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,
Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
OK, Stine. Thank you. My communication (and maybe more!) needs to improve.

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

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top