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
Hi Alexis,
Follow up to the previous question. using this measure its not giving me total.
| REPORTINGCURRENCY | ACCTINGCURRAMOUNT | ExcRateM | ReportingCurrencyMeasure | Account |
| GBP | 117611.91 | 0.458 | 53854.49359 | D12562 |
| GBP | 21571 | 0.458 | 9877.3609 | D25462 |
| GBP | 0.02 | 0.458 | 0.009158 | D25631 |
| 139182.93 |
Any idea why this behaviour. Thanks again.