Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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] ) ) )
@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
Hi @amitchandak
Yes on this
Job -> Job Register (1-M)
Job Register -> Account (M-1)
Account - >Journal Transactions (1-m)
@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.
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
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |