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 not working as expected. 

 

IDNameACCTINGCURRAMOUNTExch RateREPORTING AmountREPORTINGCURRENCY
1ACT110000110000BHD
2ACT2200500.3767538.8USD
3ACT3230440.47510945.9GBP
4ACT4345000.37612972USD
5ACT513450113450BHD
      
  101044 101044 

 

REporting amount should come as 54906.7, but it just replicate the sum of ACCTINGCURRAMOUNT. Below is my switch statement, Pls suggest some solutions.

 

REPORTING Amount= SWITCH( SELECTEDVALUE(Transactions[REPORTINGCURRENCY]), "BHD", sum(Transactions[ACCTINGCURRAMOUNT]),"USD", sum(Transactions[ACCTINGCURRAMOUNT]) * 0.376, "GBP", SUM(Transactions[ACCTINGCURRAMOUNT])* 0.475,
SUM(Transactions[ACCTINGCURRAMOUNT])
)
"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" )
    )

5 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super 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
        Super 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" )
    )
  • VeerBI's avatar
    VeerBI
    Frequent Visitor
    3 years ago

    Hi Alexis,

    Follow up to the previous question. using this measure its not giving me total.

    REPORTINGCURRENCYACCTINGCURRAMOUNTExcRateMReportingCurrencyMeasureAccount
    GBP117611.910.45853854.49359D12562
    GBP215710.4589877.3609D25462
    GBP0.020.4580.009158D25631
     139182.93   

     

    Any idea why this behaviour. Thanks again.