Solved

MS Access Pivot Table calculated values

Posted on 2013-01-16
7
426 Views
Last Modified: 2013-01-18
I have a long list of "deals" each with a %margin on an advance (the amount the deal was for), the advance and a number of other fields e.g. manufacturer, retailer, equipment_type that I want to use as filter and I want to display the data by month (in yyyymm format - this is already calculated in the data)

I need to work with the weighted margin (margin*advance) which I have also calculated in the input query.

What I want to end up with is

(sum of weighted margin)/sum of advance

for each month which recalculates as the filter fields are applied.

Is this more than can be done in Access?  :-)
0
Comment
Question by:markremms
[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
  • 4
  • 3
7 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 38782957
If I understand correctly, the answer is no because the calculation will end up as:
SUM(margin)*SUM(advance)/SUM(advance)

and not

SUM(margin*advance)/SUM(advance)

EDIT:

Wait, I think I misread. If you have already calculated the weighted margin in the query (i.e. for each row) then yes a calculated field of margin/advance should provide what you need.
0
 

Author Comment

by:markremms
ID: 38783069
So if I want to calculate detail something like:

x / sum( x)

then I can't?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 38785281
A calculated field will always aggregate all fields it depends on. If your query has already calculated the weighted margin at the detail level then the calculated field should do what you want
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:markremms
ID: 38786659
Thanks for that.  What I want to be able to do though is to use the sum to be a part of the calculation for a detail cell.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 38787099
No idea what you mean - can you give a specific example? (You can't use an overall total at a higher level if that's what you mean)
0
 

Author Comment

by:markremms
ID: 38787252
OK - sorry.  Me failing to communicate.

The original data contains values for weighted margins.

On the pivot table these will be shown in groups (with a sum for each group if I wish)

Against each weighted margin I would like to calculate its proportion of the whole group so I want to divide its value by the sum for its group.

Sounds like that might be possible if I can refer to higher level totals. :-)
0
 

Author Comment

by:markremms
ID: 38792230
Oh - you said can't...

Ah
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

690 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