I've a pivot table with results:

Washington Michigan
Apples, Oranges, Grapes Total| Apples, Oranges, Grapes Total|
234 345 23 602 198 275 5 478

I want to add a percentage of each fruit in each state

Washington Michigan
Apples, Oranges, Grapes Total| Apples, Oranges, Grapes Total|
234 345 23 602 198 275 5 478
39% 57% 4% 100% 41% 58% 1% 100%

thanks in advance.

My weird solution:

First, my source table is made of unioned queries each consisting of a
'State's' worth of 'Fruits' (each 'State' is an actual column in the source
data so they have to be unioned into one column for the pivot to work as
'State' then 'Fruit')

I had to add a new column to the pivot source data

1/(SELECT COUNT([State]) FROM sourcetable) AS PercentageOfState

and then sum the field in the pivot and format it as a percentage.


"vbasean" wrote:

I've a pivot table with results:

Washington Michigan
Apples, Oranges, Grapes Total| Apples, Oranges, Grapes Total|
234 345 23 602 198 275 5 478

I want to add a percentage of each fruit in each state

Washington Michigan
Apples, Oranges, Grapes Total| Apples, Oranges, Grapes Total|
234 345 23 602 198 275 5 478
39% 57% 4% 100% 41% 58% 1% 100%

thanks in advance.