Hello all!

This is my first post, so please kindly point out any protocol errors and I will happily correct. Thanks in advance!

I'm looking to create a "report" (generic term) that aggregates hierarchical data from multiple sources and presents it in one coherent view. However, the data is on different levels - e.g. more granular for some information than others.

Specifically, I'm looking at some sales forecasting data (sales opportunities) and trying to compare those to budget / actual numbers for the account. The revenue potential for the opportunity is listed with each opportunity, and therefore should be summed to get total opportunity revenue potential for the account. But the actual / budget numbers are per account. If I have my data source repeat the account-level numbers for each opportunity, I can do an average in the pivot table to make it show up correctly at an account level (e.g. the average of 'x' occurrences of any given number is always just that number), but then if you roll it up a level (e.g. to the account manager), it shows as the average actual / budget across that manager's accounts, not the sum.

I've attached a file which shows a simple example of this as I'm probably not describing it very well.

I would appreciate any ideas on how to solve this problem, and I do have some flexibility in reformatting the source data if that is the root of my problem (as I suspect that's at least part of the issue). Any and all help is GREATLY appreciated! Thank you!!!!!!!

Multi-level data pivot table issue example.xlsx

I'm using Excel 2013, and my ultimate source data is stored in SQL Server (2008 R2). The opportunity data is in one database, the account managers are in another database, and the Actual / Budget information is in a SQL SSAS cube, and I've currently written some VBA code to combine the source data into a format like what is in the sample spreadsheet.