Solved

Pivot help - calculated field

Posted on 2014-12-01
5
119 Views
Last Modified: 2014-12-01
experts,

I have a query as a Pivot Table view.
I used the button "Auto Calc"
It summs as expected.

I need to add another column that shows the summ as a percent of the overall total.  
I can change the Auto Calc sum to "show as percent of Grand Total" but I also lose the subtotals.  

How can I add another column that will show the percent of the overall total but keep the subtotals?  

Pivot
0
Comment
Question by:pdvsa
[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
  • 3
  • 2
5 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40473878
Add a second, duplicate Amount field in your query (definitely named), and drag that into the "Drop Totals of Detail Fields Here", and change that one to %s.
0
 

Author Comment

by:pdvsa
ID: 40473953
Philip:  that seems to be pretty close.  I drag the [Amount] into the query as a duplicate field and gave it a name then I show the field list and drag this new duplicative field into the pivot and I can show it as Percent of Grand Total but the [Amount] is duplicated.  I have 2 columns that show [Amount] but in the duplicative column I do have a percent but just need to remove the duplicate [amount].    Not sure if you follow.
0
 

Author Comment

by:pdvsa
ID: 40473959
Pivot
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40473966
Move the date to the row field - it should then disappear.Access form
0
 

Author Closing Comment

by:pdvsa
ID: 40474742
thank you Philip.  Have it working now...
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

756 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