Get the correct values when have multiple tables with indirect relationship

[kristel_tulio]

Hi, 

I would like to ask if there is a solution on how to get correct values if you have different tables and have indirect relationship.

Please see my data model below.

I do have 5 tables connected to each other and I would like to the values for my Builder Income and Builder COGS by Job Name but it's showing me the total instead not by Job name.

And also the totals need to be corrected. Thank you

Job have relationship with Job Register and Job Budget through "Job UID"

Job Register have relationship with Accounts through"Account UID"

Accounts have relationship with Journal Transactions through"Account UID"

Journal Transaction have relationship with Dates through "Date".

Calculation:

Builder Income = CALCULATE( IF( SUM( 'Journal Transactions'[Sales Calculation] ) = 0, BLANK(), SUM('Journal Transactions'[Sales Calculation] ) ) )

Builder COGS = CALCULATE( IF( SUM( 'Journal Transactions'[Cost of Sales Calculation] ) = 0, BLANK(), SUM( 'Journal Transactions'[Cost of Sales Calculation] ) ) )

[amitchandak]

@kristel_tulio , is the relation following 1-M  where

Job -> Job Register (1-M)

Job Register -> Account (1-m)

Account - >Journal Transactions (1-m)

Then I think it should work. If that is not true, have a relook at the schema design.

Prefer a star Schema

[kristel_tulio]

Hi @amitchandak 

Yes on this

Job -> Job Register (1-M)

Job Register -> Account (M-1)

Account - >Journal Transactions (1-m)

[amitchandak]

@kristel_tulio , Job Register to Account has an issue

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[kristel_tulio]

Hi @amitchandak, 

I think you're right but I'm not sure, what is wrong and how to fix it. I tried it by pulling the Job Name from Job Register, Classification from Accounts and Amount from Journal Transactions