// Keep XRates disconnected. Assuming that
// the table stores rates of conversion from
// the indicated currency to some other, but common,
// currency. In other words, the rate tells us
// the amount of the currency that you have to pay for
// 1 unit of the target currency (whatever it is,
// but for this calculation it does not matter in the
// slightest).
//
// Here's a measure that will display totals
// in any currency you want...
[Total] =
IF( HASONEFILTER( XRates[Currency] ),
var __targetCurr = SELECTEDVALUE( XRates[Currency] )
var __targetCurrXrate = SELECTEDVALUE( XRates[xrate] )
return
SUMX(
// Iterating over currencies in the current
// context to speed up the calculation. If
// we iterate over products there'll be much
// more computation. There are far fewer currencies
// than products, right?
VALUES( 'Product'[Currency] ),
// get the currency of the
// products we right now iterate over
var __originalCurr = Product[Currency]
// got the xrate for this currency
var __originalCurrXrate =
CALCULATE(
SELECTEDVALUE( XRates[xrate] ),
XRates[Currency] = __originalCurr,
ALL( XRates )
)
// get the total price of the products
// with the current currency
var __total = CALCULATE( SUM( 'Product'[Price] ) )
// express the total in the original xrate
var __valueInCommonCurr =
__total * __originalCurrXrate
// express the above total in the target currency
var __valueInTargetCurr =
__valueInCommonCurr / __targetCurrXrate
return
__valueInTargetCurr
)
)
