Forum Discussion
Pivot table with percentage calculation
Hi..
This is the first conversation, i am posting. Little hesitated before sharing, as the issues i am facing is a very small. But got stuck with this from last 2 days. Please help.
Generating a pivot table (O2:S11) with Col O - Training Place name, Col P - Number of training sessions, Col Q - Number of Type 1 Participants, Col R - Number of Type 2 Participants, Col S - Number of Type 3 Participants.
I tried for %ge of Col Q aginst Col P, %ge of Col R aginst Col P, %ge of Col S aginst Col P, as shown in table O13:S20.
Attached is the sheet for reference.
With regards
Hi aditya1986
If you want to get the result from your table O13:S20, but in Pivot table you need to use calculated fields:
1. Click inside a pivot table
2. Go to Anaylze Tab
3. Inside a calculation ribbon go to Fields, Items & Sets -> Calculated Field
4. there you will see an option to make a formula, in this case "Sum of Participant type 1 / Sessions" (column Q / Column P)
5. You can then proceed to calculate other percentages following the same logic
HOWEVER: please mind that currently values in your colum Q are actually counted dates so if you try to do step 4 you will get very wrong result. The easiest workaround is to insert a new column in your table containing 1 wherever you have a session date and use that column as "Sessions" instead of current one.
Attached is my take on what I think you tried to achieve.
Hope it helps.
BR,
2 Replies
Hi aditya1986
If you want to get the result from your table O13:S20, but in Pivot table you need to use calculated fields:
1. Click inside a pivot table
2. Go to Anaylze Tab
3. Inside a calculation ribbon go to Fields, Items & Sets -> Calculated Field
4. there you will see an option to make a formula, in this case "Sum of Participant type 1 / Sessions" (column Q / Column P)
5. You can then proceed to calculate other percentages following the same logic
HOWEVER: please mind that currently values in your colum Q are actually counted dates so if you try to do step 4 you will get very wrong result. The easiest workaround is to insert a new column in your table containing 1 wherever you have a session date and use that column as "Sessions" instead of current one.
Attached is my take on what I think you tried to achieve.
Hope it helps.
BR,
Thankyou very much Branislav1984 . The only thing that i need to be careful that all field should be SUM for field aggregations.
