Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Sign Flip

I know how to do this in SQL, but I want to see if DAX provides a better solution.  I have 2 tables, one contains accounts (ACCT) and the direction of the "sign" of that account, either -1 or 1.  The...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    I have created a sample workbook you can download here

     

    If you have a model that looks something like this. 

     

    Then you need calculations that look similar to the below:

     

    Essentially you iterate over the account sign, this in turn will project a filter down to the transaction table to the related transaction.  Then you can perform the required multiplication.

     

    Amount Calc = 
CALCULATE (
    SUMX (
        VALUES ( 'Account'[Sign] ),
        CALCULATE ( MAX ( 'Account'[Sign] ) * SUM ( 'Transaction'[Amount] ) )
    )
)

Budget Calc =
CALCULATE (
    SUMX (
        VALUES ( 'Account'[Sign] ),
        CALCULATE ( MAX ( 'Account'[Sign] ) * SUM ( 'Transaction'[Budget] ) )
    )
)
Anonymous's avatar
Anonymous
Not applicable
7 years ago

I have created a sample workbook you can download here

 

If you have a model that looks something like this. 

 

Then you need calculations that look similar to the below:

 

Essentially you iterate over the account sign, this in turn will project a filter down to the transaction table to the related transaction.  Then you can perform the required multiplication.

 

Amount Calc = 
CALCULATE (
    SUMX (
        VALUES ( 'Account'[Sign] ),
        CALCULATE ( MAX ( 'Account'[Sign] ) * SUM ( 'Transaction'[Amount] ) )
    )
)

Budget Calc =
CALCULATE (
    SUMX (
        VALUES ( 'Account'[Sign] ),
        CALCULATE ( MAX ( 'Account'[Sign] ) * SUM ( 'Transaction'[Budget] ) )
    )
)
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Perfect, thank you!