Percentage Calculation

[Guest]

Is there a way in a pivot table to calculate percentages of catagories based
on the total record?

 

[Dale Fye]

Rose,

Yes, how easy it is will be dependent on your table structure.
I just created a table that contains Student_ID, Question_Num, and Response
columns. I then populated values for 4 students and 4 questions. I then
created my cross tab query. I would normally write this query as follows to
get the number of responses of each value (A, B, C, D) for each question.

TRANSFORM Count(T1.ID) AS CountOfID
SELECT T1.Question_Num
FROM tbl_Q_Resp AS T1
GROUP BY T1.Question_Num
PIVOT T1.Response;

But if you recognize that you can determine the contribution of a single
response to the grid as 1 / (# of responses to the question), then instead
of Counting the number of records that contribute to a "cell" in this
matrix, you can sum this new value and get the percentage of each response
over each question number.

TRANSFORM Sum(1/DCount("ID","tbl_Q_Resp","Question_Num = " &
[T1].[Question_Num])) AS Expr2
SELECT T1.Question_Num
FROM tbl_Q_Resp AS T1
GROUP BY T1.Question_Num
PIVOT T1.Response;

You could take this one step farther by wrapping the Sum( ) in a format,
like:

TRANSFORM Format(Sum(1/DCount("ID","tbl_Q_Resp","Question_Num = " &
[T1].[Question_Num])), "percent") AS Expr2
SELECT T1.Question_Num
FROM tbl_Q_Resp AS T1
GROUP BY T1.Question_Num
PIVOT T1.Response;

HTH
Dale

 

[Guest]

Hi Dale,

Thanks for your help. My skill level is a bit below yours. Is there a way
to break your answer down in more simplistic terms. This is what I am trying
to do:

I have two fields’ country (Dropdown Text Field listing Countries) and
Individuals (Number field containing the amount of people from that Country).
Is there an easy way to do a percentage? I.e. How many people overall are
from a particular country?
--
Rose

Rose,

Yes, how easy it is will be dependent on your table structure.
I just created a table that contains Student_ID, Question_Num, and Response
columns. I then populated values for 4 students and 4 questions. I then
created my cross tab query. I would normally write this query as follows to
get the number of responses of each value (A, B, C, D) for each question.

TRANSFORM Count(T1.ID) AS CountOfID
SELECT T1.Question_Num
FROM tbl_Q_Resp AS T1
GROUP BY T1.Question_Num
PIVOT T1.Response;

But if you recognize that you can determine the contribution of a single
response to the grid as 1 / (# of responses to the question), then instead
of Counting the number of records that contribute to a "cell" in this
matrix, you can sum this new value and get the percentage of each response
over each question number.

TRANSFORM Sum(1/DCount("ID","tbl_Q_Resp","Question_Num = " &
[T1].[Question_Num])) AS Expr2
SELECT T1.Question_Num
FROM tbl_Q_Resp AS T1
GROUP BY T1.Question_Num
PIVOT T1.Response;

You could take this one step farther by wrapping the Sum( ) in a format,
like:

TRANSFORM Format(Sum(1/DCount("ID","tbl_Q_Resp","Question_Num = " &
[T1].[Question_Num])), "percent") AS Expr2
SELECT T1.Question_Num
FROM tbl_Q_Resp AS T1
GROUP BY T1.Question_Num
PIVOT T1.Response;

HTH
Dale

Is there a way in a pivot table to calculate percentages of catagories
based
on the total record?

 

[Guest]

Rose,

Are you trying to display this in a form or in a query?

If all you have is the two fields, then I think your query would look
something like:

SELECT Country, Individuals, _
Format([Individuals]/[TotalIndiv], "percent") as CountryPercent
FROM yourTable, _
(SELECT SUM(Individuals) as TotalIndiv FROM yourTable) as tblTotal
GROUP BY Country, Individuals

The part in parenthesis right below the FROM statement is a sub-query which
sums the Individuals column to get the TotalIndiv value. You have to wrap
this sub-query in ( ) and give it an alias name "as tblTotal". You cannot
create the subquery from the query design grid, but you will be able to view
this query in the grid after you create it in SQL view.

The alternative would be to create the sub-query first (create a new query,
add your table to the query, drag the Individuals field into the query grid,
then change the query to a totals query and change GroupBy to Sum in the
Totals row of the query grid). Running this query should give you the total
number of Individuals in a field "SumOfIndividual". Save this query as
qry_IndividualTotals.

Now create a new query, add yourTable, and qry_IndividualTotals to the grid,
but do not join them.

Add the Country, and Individual fields from yourTable, and SumOfIndividuals
from qry_IndividualTotals to the query grid. Add a computed column

CountryPercentage: [Individual]/SumOfIndividual

to the grid and run the query. If you want that formatted as a percentage,
try:

CountryPercentage: Format([Individual]/[SumOfIndividual], "percent")

HTH
Dale


--
Email address is not valid.
Please reply to newsgroup only.

Hi Dale,

Thanks for your help. My skill level is a bit below yours. Is there a way
to break your answer down in more simplistic terms. This is what I am trying
to do:

I have two fields’ country (Dropdown Text Field listing Countries) and
Individuals (Number field containing the amount of people from that Country).
Is there an easy way to do a percentage? I.e. How many people overall are
from a particular country?
--
Rose

Rose,

Yes, how easy it is will be dependent on your table structure.
I just created a table that contains Student_ID, Question_Num, and Response
columns. I then populated values for 4 students and 4 questions. I then
created my cross tab query. I would normally write this query as follows to
get the number of responses of each value (A, B, C, D) for each question.

TRANSFORM Count(T1.ID) AS CountOfID
SELECT T1.Question_Num
FROM tbl_Q_Resp AS T1
GROUP BY T1.Question_Num
PIVOT T1.Response;

But if you recognize that you can determine the contribution of a single
response to the grid as 1 / (# of responses to the question), then instead
of Counting the number of records that contribute to a "cell" in this
matrix, you can sum this new value and get the percentage of each response
over each question number.

TRANSFORM Sum(1/DCount("ID","tbl_Q_Resp","Question_Num = " &
[T1].[Question_Num])) AS Expr2
SELECT T1.Question_Num
FROM tbl_Q_Resp AS T1
GROUP BY T1.Question_Num
PIVOT T1.Response;

You could take this one step farther by wrapping the Sum( ) in a format,
like:

TRANSFORM Format(Sum(1/DCount("ID","tbl_Q_Resp","Question_Num = " &
[T1].[Question_Num])), "percent") AS Expr2
SELECT T1.Question_Num
FROM tbl_Q_Resp AS T1
GROUP BY T1.Question_Num
PIVOT T1.Response;

HTH
Dale

Is there a way in a pivot table to calculate percentages of catagories
based
on the total record?