Pivot tables - two tables with finance info

[WilliamS]

Dear fellow Chandoo readers,

The time has come for me to get out of my lurking position and ask a question.

We have to make quarterly reports to track the spend on several locations. This is my responsibility because quit frankly in the land of the blind, the one-eyed man is king. And i'm the king of Excel in our department .

I attached a simplified example of the data and the result i want. I build something similar already and it works fine with sumifs/vlookups but it is to static. The number of locations, suppliers, budgettypes etc is growing so fast that I want to recreate it with pivot tables. Then I can use those fancy slicers and I will just feel the emperor in the land of the blind! No more making custom reports!

In the file there are 3 sheets:

Payments: a simplified representation of the data received regarding payments
Budgets: a simplified representation of our budget overview
Pivot: Very basic outline of what i want. I understand the (simple) basic of pivots and i already made a nice dashboard with fancy slicers for tracking the spending (not included). But i don't know how to get the budgets in!

Can you help me in the right direction?

 

[WilliamS]

What I tried so far:

- adding a helper column in "Payments" vlookup of the year budget.
- Adding the helper column in the pivot showing the average since this is the correct value.
- This adds a column for Q1 and Q2 but i only need it after the total column. I tried hiding the unwanted columns. Works fine but when using a slicer to show for example only Q1 it goes wrong.
- Added a calculated field to determine the percentage of the budget used. Probably did it wrong because it only shows the spendQ2/budget percentage. Instead of the sum of the selected Q's/budget %.



---- update ----

Ok.

To prevent the problem with the calculated field I now added a helpercolumn in "Payments" calculating how much that invoice uses of the budget in %. This can be added to the pivot then. Looks ok but not there yet... showing the budget still is a problem.