Issues with IF and Switch statement

[VeerBI]

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. 

ID	Name	ACCTINGCURRAMOUNT	Exch Rate	REPORTING Amount	REPORTINGCURRENCY
1	ACT1	10000	1	10000	BHD
2	ACT2	20050	0.376	7538.8	USD
3	ACT3	23044	0.475	10945.9	GBP
4	ACT4	34500	0.376	12972	USD
5	ACT5	13450	1	13450	BHD
		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])

)

[VeerBI]

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.

[VeerBI]

Thank you it worked. 

[AlexisOlson]

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]

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 · Accepted solution]

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" )
    )