Budget versus Actual (relationship issues)

[tcocaro]

Hey guys,

I am trying to create a power pivot table to show ACTUAL versus BUDGET. ACTUAL & BUDGET both have ACCOUNTS (the same) however the set of accounts may differ slightly between BUDGET and ACTUAL.

I run a construction company and I may have budgeted for;

TILER $10
PAINTER $20

However in ACTUAL (taken from my XERO accounting package) it may have;

TILER $11
PAINTER $19
EMERGENCY REPAIR $10

What I want is to have the "combined" result being shown i.e.;

TILER - Budget($10) - Actual ($20)
PAINTER Budget($20) - Actual ($19)
EMERGENCY REPAIR( ) - Actual ($10)

I tried created a seperate table called ACCOUNTS which list all available accounts with the intention to use my relationship model to then get the required fields from both BUDGET and ACTUAL. The issue is the relationship direction means I cannot do this i.e. ACTUAL looks up ACCOUNTS.

This is driving me insane. I simply want to show BUDGET & ACTUAL side by side so I can apply CALCULATED fields to subtract the difference.

I am sure I am missing something basic in terms of DB logic but this has now driven me to insanity.

Regards,

TC

 

[theBardd]

The problem is that you have the measures in two tables, rather than one.

One way to resolve it, I admit it is a bit naff but it works, is to load the three tables, join them as you figured, then pull the budget and actuals into the Accounts table as calculated columns (Budget and Actual) with

=RELATED(Budget[Amount])

and

=RELATED(Actual[Amount])

and then pivot the Accounts table. You won't need CALCULATE to get the difference.

 

[tcocaro]

New image uploaded - TIS

Thanks for your assistance! Unforunately I dont think what you suggest works, I have attached ER diagram of what I have.

"idk" is made up of [job]&

. Job being a construction project and code being the account code e.g. Painting might be 101. This is how i uniquely identify my records.

However the accounts table doesnt have such a link\reference to job to create an idk. Accounts is merely a list of ALL the account codes from my accounting package.

If i reference "accounts" to any table it becomes the lookup table. In short "[COLOR=#574123]=RELATED(Budget[Amount])" will give an error.

Hope this makes some more sense?[/COLOR]

 

[tcocaro]

Giving it some more thought I think Ideally what I would like to do is dynamically create a new table, call it, merge_table and get the unique (idk) records from "InvoiceLines" and "Budget & CP". For instance if I could get the columns, Tracking_Name\Jobs, LineAmount\LineAmount then this would solve all my issues.

I understand I can manually merge\unique records but this isnt what I am after, I was hoping pivottables would solve most of the manual tasks for me.

Any ideas?

 

[theBardd]

Why don't you post the workbook?

 

[tcocaro]

Hi theBardd,

Thanks for offering to look over this, I have lost my hair with this. The workbook can be downloaded from, job analysis pivot table.xlsx - Send Files Online - TempSend.com

A solution can be to replicate "Accounts" data over and over again for each JOB. This way each JOB has a list of ALL accounts which I can then link to InvoiceLines and Budget. I just hoped there was a way to limit my manual involvement as there are a lot of jobs and a lot of fiddling around.

If I do a Pivot table WITHOUT a relationship it shows the data exactly as I want it i.e. account - acutal amount - budget. The issue ofcourse is I cannot do any calculations between them as they are simply shown side by side but no relationship between.

Hope you can find me a solution, I would be very grateful.

Regards,

TC

 

[theBardd]

Is Accounts linked to Budget or Invoice at all, if so what field (AccountID to InvoiceID?)?

Is InvoiceLines meant to be what you called Actual earlier?