| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percent of a value within all values, within a pivottable
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Percent of a value within all values, within a pivottable
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. -- HTH... Jim Thomlinson "Mark Parent" wrote: 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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Percent of a value within all values, within a pivottable
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" wrote: 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. -- HTH... Jim Thomlinson "Mark Parent" wrote: 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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Percent of a value within all values, within a pivottable
Nine provinces and two territories but who's counting...
Perhaps leave the provinces across the top and move the flags down? -- HTH... Jim Thomlinson "Mark Parent" wrote: 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" wrote: 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. -- HTH... Jim Thomlinson "Mark Parent" wrote: 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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Percent of a value within all values, within a pivottable
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! "Jim Thomlinson" wrote: Nine provinces and two territories but who's counting... Perhaps leave the provinces across the top and move the flags down? -- HTH... Jim Thomlinson "Mark Parent" wrote: 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" wrote: 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. -- HTH... Jim Thomlinson "Mark Parent" wrote: 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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Percent of a value within all values, within a pivottable
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Percent of a value within all values, within a pivottable
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" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Percent of a value within all values, within a pivottable
|
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Percent of a value within all values, within a pivottable
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 "Herbert Seidenberg" wrote: Replaced cust with TO and modified formula: http://www.savefile.com/files/1618891 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pivottable: Show value as a percent of Subtotal | Excel Discussion (Misc queries) | |||
| Computing percent changes in Excel between two values | Excel Discussion (Misc queries) | |||
| How can I change all values in a spreadsheet by a certain percent | Excel Discussion (Misc queries) | |||
| PivotTable - Percent of Current Total | Excel Discussion (Misc queries) | |||
| Is there a way to have two values (percent and number) in a label. | Charts and Charting in Excel | |||
Linear Mode
