Forum Discussion
VeerBI
3 years agoFrequent Visitor
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 ...
- 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
3 years agoFrequent Visitor
Thank you it worked.