Pivot Table Calculated Fields - Percentages

[Dormie]

Hi, I'm using access 2010.

I have a pivot table with the rows as months/days with the detail fields being sales, cogs, expanses, etc. I'm trying to add a field as a percent of two of them. I can calculate it fine in both the query or pivot table however in the pivot table it always totals them days, months, then grand total. I'm looking for it to maybe do each row individually.

For example:
In Query Day 1 has
Sale 1 - 25%
Sale 2 - 25%
Sale 3 - 25%
Day 2 has


Sale 1 - 50%
Sale 2 - 50%
Sale 3 - 50%

In the Pivot Table it shows:
Day 1 - 75%
Day 2 - 150%
Total - 225%

[June7]

Don't understand what you mean by 'each row individually'. Exactly what do you want to total? Show example of desired output.

[Dormie]

Like always thanks for your help June7!

Basically my Pivot table is set up only showing months and grand total.

Looks like this:
Sales COGS Profit Margin %
Jan 1000 500
Feb 500 250
March 250 125
Grand Total 1750 875

Currently the Profit Margin % is a column in the query and runs each sale individually fine.

When it comes to the Pivot Table the Profit Margin Column sums/adds all the percents per sale for the day, then sums each day for the month total, then each month for a grand total. Instead I'm looking for the actual percent per month and the grand total percent (Jan Total COGS/ Jan Total Sales), Jan (500/100).

I've tried doing the calculation in the pivot table however I'm not too familiar with that method and I believe Iget the same result.

Thank you!

[June7]

I don't use pivot tables/charts. I am not sure can do what you want in one. It could be possible in a report based on a CROSSTAB query. However, building report based on CROSSTAB to run perpetually is not easy because of the dynamic nature of CROSSTAB output fields.