Pivot table: Variable divided by another variable within the SAME yearquarter.

[jasonleewkd]

Hi all, as above. Pivot table allows "Show values as... % of.. % of parent row total.. % of grand total" etc etc.


For my situation however, none of the excel pivot defined functions work. Anyone has any idea insight on whether pivot table has such a function or vba needs to be used?


The file link is here - https://docs.google.com/open?id=0BzQyFnW9qIEXalNFMGprTFZham8


Thank you all you ninjas!

 

[keymaster]

You can use Calculated Fields option to do this. Follow below steps:


1. Select any value field inside pivot

2. Click on from Home ribbon > Insert > Calculated field

3. Write the field name as Launch vs. Sold % (or whatever you fancy)

4. Write formula as ='Uncompleted units Launched in the Quarter'/'Uncompleted Units Sold in the Quarter'

5. click ok

6. Now add this new field to pivot values (remove the other 2 if you dont need them)

7. Done!


See this file: http://img.chandoo.org/playground/Residential%20Data%20%28Jason%29.xlsx


For help on calculated fields visit - http://chandoo.org/wp/2010/01/27/pivot-table-tricks/#calculated_fields

 

[jasonleewkd]

That was awesome chandoo! Thanks so much. However, it should be Home ribbon > Options under PivotTable Tools > Fields, items, & Sets.


Then again, that's a small mistake. Thanks you've replaced 2 hours of work with 5 mins of excel!


On another note. Do you intend to come to Singapore?

 

[keymaster]

@Jason.. my bad. I am using Excel 2010 in which you can insert calculated fields from home ribbon itself.


And yes, I hope to revisit Singapore, although not sure when. I will post an update on blog once the dates are finalized.