Percent of a value within all values, within a pivottable

[Mark Parent]

This seems like such a simple idea, I'm surprised I can't find a way to
handle it...

Period Cust Flag Prov
200805 123 Y ON
200805 234 Y ON
200805 345 N ON
200805 456 Y BC
200805 567 N BC

I've built a pivottable on the data above, with Period forming the rows,
Prov and Flag the columns, and Count of Cust in the data section. For
200805, in ON I see 2 customers with flag=Y and 1 with flag=N. In BC, I see
1 customer with flag=Y and 1 with flag=N.

I would like to see each of these values as a percentage of the values
within that province and period. For example, for 200805 in ON I'd like to
see 67% and 33%, in BC, I'd like to see 50% and 50%.

I've tried to use the field settings, and I can show these figures as
percentages of the row or column total, but not as percentages of the number
within the period and province. I can move the province field from a column
to a row, and then display percentage of row total, but the layout isn't
condusive, because there are lots of provinces and periods.

It strikes me as a simple request, to see the count of some value within all
possible values for that field, as a percentage. I've convinced myself that
I'm just missing the right function or field setting.... Is there a
solution?

 

[Jim Thomlinson]

If you change your layout a bit it is fairly easy.

Move either your Provinces or your flags down into the rows. Now right click
on the customer counts and select field setting -> option and change to % of
row. Left as it is you have a bit of an up hill battle.

 

[Mark Parent]

Hi Jim:

I have tried moving the provinces to the rows, and using & of row total as
you suggest, but the layout is not condusive to the people reviewing the
data. Because there are a dozen provinces and loads of periods, it's not as
easy to compare as when the results are spread across.

I should correct my wording, where I stated "It strikes me as a simple
request" I should have said something like "It strikes me as a natural
request" !

 

[Jim Thomlinson]

Nine provinces and two territories but who's counting...

Perhaps leave the provinces across the top and move the flags down?

 

[Mark Parent]

Ten provinces (since 1949) and three territories (since 1999) but who's
counting!

I've worked around the problem by creating additional fields FlagY and
FlagN, which contain 1 or 0 if the flag is Y or N. I created a calculated
field as FlagY/(FlagY+FlagN), and then included the calculated field in the
pivottable. This seems to be working properly.

I'm still suspicious that there's a simpler, more natural way to express
this, but given the responses, perhaps this can only be achieved with a work
around.

Thanks for your ideas!

 

[Herbert Seidenberg]

Add a helper column to your source data:
=1/SUMPRODUCT((Period=Period R)*(Prov=Prov R))
and sum on that. File at:
http://www.savefile.com/files/1615244

 

[Pair_of_Scissors]

Hi there,

I checked your file, and I came a bit further also... same problem here...

I'll take your sheet as an example again. Let's sat that customer is
turnover now, and the second PivotTable is the SUM of all the turnovers
instead of COUNT. Can I get percentages in the first Pivottable then as well?
What should I change in the SUMPRODUCT-formula?

 

[Herbert Seidenberg]

Replaced cust with TO and
modified formula:
http://www.savefile.com/files/1618891

 

[Pair_of_Scissors]

very interesting... i used to find out these things myself, but i didn't work
with excel this way for seven years or so... the creativity is gone a bit
haha...

but curious: could you explain for a bit what you did?

rody