Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel Advanced Pivot / Calculated Field

Posted on 2013-06-13
5
Medium Priority
?
726 Views
Last Modified: 2013-06-14
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
0
Comment
Question by:kwieckii
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 39245380
This is easiest if you create a "helper" column in your source data.

Please see the attached file:

Q-28156571.xlsx

1) I used a Table for the source data

2) I added a helper column, "Result", with formula...

=[@Total]/AVERAGEIFS([New Class Sum],[Old Class],[@[Old Class]],[New Class],[@[New Class]])

3) I created a PT with Old Class and New Class as the row fields, and Sum of Old Class, Avg of New Class, and Sum of Result as the data fields

4) I turned off subtotals for Old Class


You might also like to review this article: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3172-How-to-Automatically-Update-Your-PivotTables-and-PivotCharts.html
0
 

Author Comment

by:kwieckii
ID: 39245672
That was a good idea - adding the result column !!  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]  ?

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.
0
 
LVL 10

Expert Comment

by:Gašper Kamenšek
ID: 39245843
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...
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39246017
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]  ?

Brain cramp?  :)

Yes, your simpler formula should work just fine.
0
 

Author Comment

by:kwieckii
ID: 39246569
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.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question