Excel 2013 Pivot Table

[kgw2511]

I have created a pivot table, using data from two tables but rather than just expressing totals, I want to show one number as a percentage of the other, i.e. 40%, 30%, 35% in the table below.

Can this be done?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Female[/TD]
[TD][/TD]
[TD]Male[/TD]
[TD][/TD]
[TD]Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Distinct Count[/TD]
[TD]Number in Cohort[/TD]
[TD]Distinct Count[/TD]
[TD]Number in Cohort[/TD]
[TD]Distinct Count[/TD]
[TD]Number in Cohort[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]50[/TD]
[TD]15[/TD]
[TD]50[/TD]
[TD]35[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]

Thanks

 

[RudiS]

You could create a calculated field in the Pivot: Distinct Count / Number in Cohort

 

[kgw2511]

Thanks for your reply Rudi, however I didn't think it was possible to use calculated fields in pivot tables which use data from two different tables? When I look at creating a calculated field, the menu item is greyed out.

 

[RudiS]

I didn't consider that, but its probably true as a pivot table becomes very limited in options with multiple data sources. Is there any chance that you can combine the records from these two sources and then create the PT? Alternative, have a look into using the Power Pivot add-in, or using VLOOKUP to combine the data from table 2 into table 1 (incl. the calculated values) and then use PT's to report it.