Actual versus Budget joins

[lmheath]

Hi,

New to Looker, I have used power BI a lot previously and trying to work out how to manage joins / as opposed to relationships in Power BI.

The basic setup is that i have three tables:

Nominal_codes - the primary key is nominal code - i.e. the type of transaction - with some additional attributes, is it income, cost etc

Actual_transactions - list of actual transactions - the foreign key is nominal code - many to one with the nominal code key above

Actual measure - is the sum of the value of the transactions

Budget_transactions -list of budget transactions - the foreign key is nominal code - many to one
with the nominal code key above

Budget measure - is the sum of the value of the budget transactions

I explore nominal codes and join to actual results and budget results by the nominal code -left outer - many to one

I can individually look at the two measures - However, when i try to get look at the actual and budget measures side by side i get an error

any suggestions?

Thanks,
Laurence

[Brett_Sauve]

Hey @lmheath, are actual_transactions and budget_transactions related in any way? Or, are they two unrelated sets of transactions?

I think the confusion you’re having is probably that all 3 tables have a common key of nominal_code, and so you have a circular set of relationships around nominal_code like this:

                  actual_transactions
                   /               \
                  /                 \
            nominal_code          nominal_code
                /                     \
               /                       \ 
budget_transactions--nominal_code--nominal_codes

You don’t have to (and probably shouldn’t) model all of these relationships simultaneously.
My guess is that you’re going to want either actual_transactions and/or budget_transactions to be your explore, and not nominal_codes. This is because nominal_codes is just some metadata about a transaction and not the actual transactions that you’re probably interested in.