Heres a Challenge (formula for 'Percentage of column total')

[Terry Adlem]

Here's the problem.

We have a query, running off a table that we are viewing in a pivot
table.

There are 3 site Bristol, London and Macclesfield.
Each is a call centre that records how many of the calls taken had
information posted to them afterwards.

so table looks a little like this:

info posted not posted Total calls
Bristol 10 2 12
Macclesfield 11 4 15
London 4 56 60

ok, hope that showed up ok.

We want to work out the percentage of how many calls had info posted
and not posted - making the total calls = 100%

There is a filter thing built into Access XP that you can run called
'Percentage of column total'. You can add this in through customising
your toolbars and it's under the pivottable/pivotchart menu.

Basically I need the formula that would be used to create the same
affect as this filter/button does.

The problem being that when viewing the table as a pivot chart and
inserting the percentage data label - you cannot format this label i.e
to 2 decimals places.

Working out the formula and creating a calculated total in a pivot
table and then viewing the labels as values, is the only way I can
think to resolve this.

Many Thanks in advance.

Terry

(e-mail address removed)

 

[John Nurick]

Hi Terry,

If I understand you right you can do it with a subquery that calculates
the totals inside a query that calculates the ratios. Something like

SELECT s_InfoPosted/s_TotalCalls * 100 AS PC_InfoPosted,
s_NotPosted/s_TotalCalls * 100 AS PC_NotPosted
FROM (
SELECT Sum(InfoPosted) AS s_InfoPosted,
Sum(TotalCalls) As s_TotalCalls
FROM Sales_Data
);