Calculated Field question for pivot tables

[dcleslie]

I have a pivot table pulling the count of attendees for various events. Of those, another column pulls a count of those who have applied. I need to insert a calculated field to find the percentage of those that applied based off of the total who attended the event.

Should be simple to do, but when I try, I get a %DIV=0! value in the cells.

There are other work-around I could try, but the pivot table is much more complex than just these two data points, I need to have more than one calculated field inserted, the pivot table will have splicers and the table is pulling from a dynamic data set (connected through a SQL Server).

Thoughts? am I missing something obvious?

 

[steve the fish]

The way i would approach this is to use 1s and 0s for attended and applied. Calculated fields use sum so using 1s and 0s basically turns a sum into a count.

 

[dcleslie]

I tried that and having the field be a sum or count is still causing me issues.

 

[steve the fish]

So the attended column has 1s and 0s? Ditto the applied column?

 

[Fazza]

Maybe customise the SQL (that defines the dataset into the pivot cache/table) to get the desired bespoke result.

For a manually created pivot table, if you set up the pivot table via the wizard (ALT-D-P), choose external data source at the first step, and then at the last step (of the wizard) choose the option to edit the SQL.

PS. Reason for this is you can be very specific about what you want.