I'm creating a 2018 budget spreadsheet against which I'll track invoices for burndown charts. My budget rows are created as
LineID Vendor etc etc Jan Feb Mar ... Dec Total
XY001 IBM $50 $50 $100 $50 $xxx
AM005 Whole Foods $25 $35 $45 $100 $xxx
etc
When I pull the data into the model I naturally unpivot on the calendar dates. When I receive invoices my unpvioted burndown measures can tell what my cumulative budget and invoiced amounts are. When my monthly Whole Foods invoices arrive I want to link them to the AM005 budget line for easy pivot table comparisons instead of the horrendous formulas I had in my 2017 budget version.
However, unpivoting duplicates the unique LineID against which I want to match my invoices so I can't create a relationship in PowerPivot. Without the relationship I can't put my budget and invoice burndown measures into the same pivot table and have a single set of slicers give me breakdowns by vendor, department, etc.
I suspect I have to extract all my budget key fields into a unique lookup table that links to both the budget and invoice tables, but I'm hoping there's a special trick I've missed where I could avoid that.
LineID Vendor etc etc Jan Feb Mar ... Dec Total
XY001 IBM $50 $50 $100 $50 $xxx
AM005 Whole Foods $25 $35 $45 $100 $xxx
etc
When I pull the data into the model I naturally unpivot on the calendar dates. When I receive invoices my unpvioted burndown measures can tell what my cumulative budget and invoiced amounts are. When my monthly Whole Foods invoices arrive I want to link them to the AM005 budget line for easy pivot table comparisons instead of the horrendous formulas I had in my 2017 budget version.
However, unpivoting duplicates the unique LineID against which I want to match my invoices so I can't create a relationship in PowerPivot. Without the relationship I can't put my budget and invoice burndown measures into the same pivot table and have a single set of slicers give me breakdowns by vendor, department, etc.
I suspect I have to extract all my budget key fields into a unique lookup table that links to both the budget and invoice tables, but I'm hoping there's a special trick I've missed where I could avoid that.