Forum Discussion

VeerBI's avatar
VeerBI
Frequent Visitor
3 years ago
Solved

Issues with IF and Switch statement

Dear All,   DAX Newbie here, I am performing calculations based on exchange rates for multiple currencies, I have tried with nested IF and Switch statement the calculations are fine but the sum is ...
"DAX"
@Dax
DAX
ifelse
  • AlexisOlson's avatar
    AlexisOlson
    3 years ago

    You don't necessarily need to use SUMX at the transaction-level granularity but you do need to iterate over multiple exchange rates somehow.

     

    You could try this instead:

    REPORTING Amount =
SUMX (
    VALUES ( Transactions[REPORTINGCURRENCY] ),
    SWITCH (
        Transactions[REPORTINGCURRENCY],
        "USD", 0.376,
        "GBP", 0.475,
        1
    )
        * CALCULATE (
            SUM ( Transactions[ACCTINGCURRAMOUNT] )
        )
)

     

    Or you could manually iterate over each currency like this:

    REPORTING Amount =
CALCULATE (
    SUM ( Transactions[ACCTINGCURRAMOUNT] ),
    KEEPFILTERS ( NOT Transactions[REPORTINGCURRENCY] IN { "USD", "GBP" } )
)
    + CALCULATE (
        SUM ( Transactions[ACCTINGCURRAMOUNT] ) * 0.376,
        KEEPFILTERS ( Transactions[REPORTINGCURRENCY] = "USD" )
    )
    + CALCULATE (
        SUM ( Transactions[ACCTINGCURRAMOUNT] ) * 0.475,
        KEEPFILTERS ( Transactions[REPORTINGCURRENCY] = "GBP" )
    )
AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
3 years ago

You need to iterate over the rows and apply the exchange rate to each row, not after you've already summed the transactions.

 

REPORTING Amount =
SUMX (
    Transactions,
    SWITCH (
        SELECTEDVALUE ( Transactions[REPORTINGCURRENCY] ),
        "USD", 0.376,
        "GBP", 0.475,
        1
    ) * Transactions[ACCTINGCURRAMOUNT]
)
VeerBI's avatar
VeerBI
Frequent Visitor
3 years ago

Thanks for the solution. I cannot use SUMX, as Business logic requires me to use the exchange rate on the sum of Transactions[acctingcurramount]. as individual transactions will have individual exchange rates, it will give wrong reporting amount. Please let me know if there is any other way i can work this out.

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    3 years ago

    You don't necessarily need to use SUMX at the transaction-level granularity but you do need to iterate over multiple exchange rates somehow.

     

    You could try this instead:

    REPORTING Amount =
SUMX (
    VALUES ( Transactions[REPORTINGCURRENCY] ),
    SWITCH (
        Transactions[REPORTINGCURRENCY],
        "USD", 0.376,
        "GBP", 0.475,
        1
    )
        * CALCULATE (
            SUM ( Transactions[ACCTINGCURRAMOUNT] )
        )
)

     

    Or you could manually iterate over each currency like this:

    REPORTING Amount =
CALCULATE (
    SUM ( Transactions[ACCTINGCURRAMOUNT] ),
    KEEPFILTERS ( NOT Transactions[REPORTINGCURRENCY] IN { "USD", "GBP" } )
)
    + CALCULATE (
        SUM ( Transactions[ACCTINGCURRAMOUNT] ) * 0.376,
        KEEPFILTERS ( Transactions[REPORTINGCURRENCY] = "USD" )
    )
    + CALCULATE (
        SUM ( Transactions[ACCTINGCURRAMOUNT] ) * 0.475,
        KEEPFILTERS ( Transactions[REPORTINGCURRENCY] = "GBP" )
    )