Calculate SUM ( amount ) - one column related to two other columns

[odzelashvili1]

Hi Power BI community 🙂 

I have this relationship, dim_accounting_accounts ( id ) is related to fact_accounting_transactions ( credit_account_id (active) & debit_account_id (deactive))

I want to SUM fact_accounting_transaction ( amount ) where dim_accounting_accounts ( account_type_id ) is X for fact_transaction_accounts (credit_account_id) and  dim_accounting_accounts ( account_type_id ) is Y for fact_transaction_accounts (debit_account_id).

any suggestions? 

[v-kkf-msft · Accepted solution]

Hi @odzelashvili1 ,

Please try the measure.

Measure = 
VAR tab1 =
    CALCULATETABLE (
        fact_accounting_transactions,
        dim_accounting_accounts[account_type_id] = "x"
    )
VAR tab2 =
    CALCULATETABLE (
        fact_accounting_transactions,
        dim_accounting_accounts[account_type_id] = "y",
        USERELATIONSHIP ( dim_accounting_accounts[id], fact_accounting_transactions[debit_account_id] )
    )
RETURN
    CALCULATE (
        SUM ( fact_accounting_transactions[amount] ),
        INTERSECT ( tab1, tab2 )
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[odzelashvili1]

I can do this with this way, but I want not to add calculate columns to my tables:

aa_debit_account = 
CALCULATE (
    MAX ( dim_accounting_accounts[account_type_id] ),
    FILTER (
        dim_accounting_accounts,
        dim_accounting_accounts[id] = fact_accounting_transactions[debit_account_id]
    ),
    USERELATIONSHIP ( dim_accounting_accounts[id], fact_accounting_transactions[debit_account_id] )
)

aa_credit_account = 
CALCULATE(
    MAX(dim_accounting_accounts[account_type_id]),
    FILTER(
        dim_accounting_accounts, dim_accounting_accounts[id] = fact_accounting_transactions[credit_account_id]))

and after this Sum (amount) where aa_credit_account = x and aa_debit_account = y.

[tamerj1]

@odzelashvili1 
The active relationship will do that automatically. Did you try my code? it should give the desired results.

[tamerj1]

Hi @odzelashvili1 

Please try

=
VAR SumCredit =
    SUM ( fact_accounting_transaction[amount] )
VAR SumDebit =
    CALCULATE (
        SUM ( fact_accounting_transaction[amount] ),
        USERELATIONSHIP ( dim_accounting_accounts[id], fact_transaction_accounts[debit_account_id] )
    )
RETURN
    SumCredit + SumDebit

[odzelashvili1]

This is not what I meant.

I want to sum (amount) where credit_account_id = x and debit_account_id = y same time. But this x and y must be filtered by another table (dim_accounting_accounts) which is related to this debit and credit columns. 

[tamerj1]

@odzelashvili1 
Can you advise how your visual look like? How do you select X and Y? Or you just want to hard code certian values of X and Y?