kwieckii
asked on
Excel Advanced Pivot / Calculated Field
Basically, what I'm trying to do is to present data in a pivot table, but include summary results of a different group. See attachment for full example.
I'd like to have the results that are in parenthesis in the example below as the summary of the pivot (% of Old Class in New Class).
Detail of Data:
Inv ID Description Old Class New Class Total New Class Sum
1 Dodge Car 4-Wheeler $5,000 5000
2 Mongoose Bike 2-Wheeler $200 1000
3 Yamaha Motorcycle 2-Wheeler $700 1000
6 Vespa Scooter 2-Wheeler $50 1000
4 Vespa Scooter 2-Wheeler $50 1000
5 Can Am Motorcycle 3-Wheeler $80 80
What I'd like to see in a pivot table:
Old Class / New Class (% of Old Class in New Class)
Bike
2-Wheeler 200 / 1000
Car
4-Wheeler 5000/5000
Motorcycle
2-Wheeler 700/1000
3-Wheeler 80/80
Scooter
2-Wheeler 100/1000
I'm able to put the numerator and the denominator as different summary values in the pivot. What I'm not able to do is to divide the two.
I've also tried to add a calculated field and noticed that when I count a calculated field, I get a different result from using the summary type of count in the pivot, but the sum of a calculated field gives the same result as the sum of the pivot summary type. (see attachment for better explanation).
Pivot-Example.xlsx
I'd like to have the results that are in parenthesis in the example below as the summary of the pivot (% of Old Class in New Class).
Detail of Data:
Inv ID Description Old Class New Class Total New Class Sum
1 Dodge Car 4-Wheeler $5,000 5000
2 Mongoose Bike 2-Wheeler $200 1000
3 Yamaha Motorcycle 2-Wheeler $700 1000
6 Vespa Scooter 2-Wheeler $50 1000
4 Vespa Scooter 2-Wheeler $50 1000
5 Can Am Motorcycle 3-Wheeler $80 80
What I'd like to see in a pivot table:
Old Class / New Class (% of Old Class in New Class)
Bike
2-Wheeler 200 / 1000
Car
4-Wheeler 5000/5000
Motorcycle
2-Wheeler 700/1000
3-Wheeler 80/80
Scooter
2-Wheeler 100/1000
I'm able to put the numerator and the denominator as different summary values in the pivot. What I'm not able to do is to divide the two.
I've also tried to add a calculated field and noticed that when I count a calculated field, I get a different result from using the summary type of count in the pivot, but the sum of a calculated field gives the same result as the sum of the pivot summary type. (see attachment for better explanation).
Pivot-Example.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
With a calculated column in a pivot table, you can only do one thing, SUM. You can change the function to Count or to anything you like and it will let you but it will not do anything else than SUM and the result will stay the same...
I'm wondering - what's the reason for using this formula:
=[@Total]/AVERAGEIFS([New Class Sum],[Old Class],[@[Old Class]],[New Class],[@[New Class]])
and not
[@Total] / [@New Class Sum] ?
=[@Total]/AVERAGEIFS([New Class Sum],[Old Class],[@[Old Class]],[New Class],[@[New Class]])
and not
[@Total] / [@New Class Sum] ?
Brain cramp? :)
Yes, your simpler formula should work just fine.
ASKER
MP - thanks - just checking in case I missed something.
Gasper - it's strange that they call it a calculated field if the only calculation you can perform is a sum. Looking around, it seems the calculated field can support formulas (other than sum), but it probably doesn't work for what I'm trying to accomplish. I found a good link (below) that explains the usage of calculated fields and I'll be reading it along with MPs post this weekend.
http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=141:excel-pivot-tables-insert-calculated-fields-a-calculated-items-create-formulas&catid=81&Itemid=486
Thanks for the help - closing this one out.
Gasper - it's strange that they call it a calculated field if the only calculation you can perform is a sum. Looking around, it seems the calculated field can support formulas (other than sum), but it probably doesn't work for what I'm trying to accomplish. I found a good link (below) that explains the usage of calculated fields and I'll be reading it along with MPs post this weekend.
http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=141:excel-pivot-tables-insert-calculated-fields-a-calculated-items-create-formulas&catid=81&Itemid=486
Thanks for the help - closing this one out.
ASKER
=[@Total]/AVERAGEIFS([New Class Sum],[Old Class],[@[Old Class]],[New Class],[@[New Class]])
and not
[@Total] / [@New Class Sum] ?
Also - any idea why the Calculated Field Count is different than the Pivot Count result?
Another solution is to add another helper column (I named base)- with the value of 1 for all rows. I was then able to create a calculated field that worked correctly using the sum(base) instead of count(inv id)
Thanks for the link to the article - planning on setting some time this weekend to read.