New here, apologies in advance as I suspect this is something that others have experienced but I think I'm missing the right terminology to properly search for a solution...
I have created several PowerPivot reports that pull several tables together to track spending on various projects at our company. There are tables for our actual spend, outstanding purchase orders, project information, cost codes, and budget/forecast values. I have had to create two sheets in the workbook:
- Sheet 1 - Shows budget/forecast, actual spent, and outstanding purchase orders by project and cost code
- Sheet 2 - Shows actual and outstanding purchase orders by project, cost code, and purchase order number
I created two sheets because I haven't figured out how to consolidate into one report. The problem is the mismatch between budget/forecast data and the actual and outstanding purchase order data. The actual/PO data has multiple records under each cost code, representing each transaction, while the forecast/budget data is only broken out to the cost code level. As a result, when I try to show budget/forecast with actual and outstanding purchase order detail, the report breaks down because there's no relationship between the budget/forecast data and the purchase order numbers (aside from the cost code, but that just means the budget/forecast number for a cost code is repeated for each PO under that cost code).
I'm thinking/hoping that there's some way to stop the PowerPivot from trying to match the budget/forecast detail to the PO level detail, basically halting the records returned in the budget/forecast column at a subtotal level for the cost code, rather than trying to associate the budget/forecast for that cost code to each individual PO under that cost code.
Thanks in advance!
Bookmarks