# Pivot calculated field based on data columns

#### STGE2

##### New Member
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

Best regards
Stine

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
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)

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.

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

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

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

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

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

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

Replies
4
Views
426
Replies
0
Views
130
Replies
5
Views
353
Replies
1
Views
722
Replies
0
Views
269

1,219,518
Messages
6,148,747
Members
450,832
Latest member
Tyroneb90

### 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.

### Which adblocker are you using?

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

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