Excel Calculated fields on pivot table fields

Question

I have a base data field:

Presenting Complaint | Diagnosis
complaint a          | <blank>
complaint b          | diagnosis a
complaint c          | diagnosis b

I have a pivot table:

Presenting complaint | Count of diagnosis | Count of presenting complaint
complaint a          |                    | 1
complaint b          | 1                  | 1
complaint c          | 1                  | 1

I want to create a custom field which basically counts percentage diagnosis made, taking account of the two calculated pivot fields ('count of diagnosis') and ('count of presenting complaint'). Is this possible?

Answer 1

You won't be able to calculate the percentages inside the pivot. Though, you can use the GetPivotData() function to calculate the percentages outside of the pivot table.

To calculate the percentage for a particular complaint you can use:

=GETPIVOTDATA("Count of  Diagnosis",$E$3,"Presenting Complaint ","complaint a")/GETPIVOTDATA("Count of Presenting Complaint ",$E$3,"Presenting Complaint ","complaint a")

For the Grand Total you can use:

=GETPIVOTDATA("Count of  Diagnosis",$E$3)/GETPIVOTDATA("Count of Presenting Complaint ",$E$3)

$E$3 is the position of the pivot table.

While this looks cumbersome, the advantage is that when the structure of the pivot changes by adding data or adding columns, you won't have to change your formula. It will always select the items you specify in the formula.

It's also easy to use. When you enter an equal sign, = , and click on a value in the pivot, excel will automatically enter the formula for you.