Pivot Table - running expense vs fixed budget

[Jerfyjer169]

Hi all,

I have researched on pivot table and seem not to find a solution to my question.

My task is to track the progress of a project, and the project is divided into different jobs.

The excel file consists of two tables. Table 1 shows detailed information of each invoice received and is the YTD running total of expense spent, and Table 2 is a list of jobs with allocated budget.

I would want to merge the two tables in one pivot table so I can see a simple YTD vs Total Budget. The problem I have is that every time the same job appears in Table 1, Budget adds up to a bigger number in the Pivot Table.

Thanks in advance!!

 

[cyrilbrd]

Hi, what is the common denominator in between each table, the job number?

 

[cyrilbrd]

example: table 1

	A	B	C
job	invoice	amount

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=DAE7F5"" target="_blank"></colgroup><colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1015[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1903[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2180[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3087[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3285[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4762[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1735[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1392[/TD]

</tbody>

Sheet1​

Table 2:

	E	F
job	budget

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=DAE7F5"" target="_blank"></colgroup><colgroup><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12000[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12000[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]15000[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]15000[/TD]

</tbody>

Sheet1​

Pivot table:
[TABLE="width: 292"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]Sum of amount[/TD]
[TD]Sum of budget[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]8864[/TD]
[TD="align: right"]12000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]6923[/TD]
[TD="align: right"]12000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]3572[/TD]
[TD="align: right"]15000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD="align: right"]15000[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]19359[/TD]
[TD="align: right"]54000[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jerfyjer169]

Hi Cyrilbrd,

Yes, the common denominator is job number. Your sample pivot table is exactly what I would like to do!

Much appreciated if you could please provide a guide on how to produce above table!!

Thanks in advance

 

[cyrilbrd]

What version of excel are you using?

 

[Jerfyjer169]

im using excel 2016

 

[cyrilbrd]

Ok
First select any table and create a pivot table (insert PT)
Second select "Add this data to the Data Model" Located on the bottom of the popup.
Third in the Field list that will appear on the right (most likely) under the word PivotTable Fields, you will notice two words, Active and All.
Click All
The two tables should now be present...
Select table 2 (budget) Place Job in the Rows.
Place Budget in the Values
Select table 1 (invoice) Place amount in the Values
A yellow notification should have appeared by now stating the following: Relationships between tables may be needed.
Click Auto-Detect...
A popup will appear click close OR adjust the relationship if required... The second table MUST not have duplicates of job#...
And now your relationship has been created.
Would that work for you?

 

[Jerfyjer169]

Hi

had a go, and it worked perfectly!

Thanks cyrilbrd!!

 

[cyrilbrd]

Hi

had a go, and it worked perfectly!

Thanks cyrilbrd!!

Most welcome and thank you for your feedback.