Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

MS Access Pivot Table calculated values

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
markremms
Asked:
markremms
  • 4
  • 3
1 Solution
 
Rory ArchibaldCommented:
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
 
markremmsAuthor Commented:
So if I want to calculate detail something like:

x / sum( x)

then I can't?
0
 
Rory ArchibaldCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
markremmsAuthor Commented:
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
 
Rory ArchibaldCommented:
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
 
markremmsAuthor Commented:
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
 
markremmsAuthor Commented:
Oh - you said can't...

Ah
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now