Currency conversion at totals: SELECTEDVALUE, blended rates and the SUMX you actually need

Sales in EUR is correct for France, correct for Japan, and wrong on the total row. Or the total is fine until someone adds a month slicer. Either way the numbers on the rows do not add up to the number at the bottom, and the report loses credibility in one meeting.

The measure picks one exchange rate with SELECTEDVALUE (or MAX, or a lookup) and multiplies the total amount by it. On a row where one currency is in context there is one rate, so it works. On the total there are many currencies: SELECTEDVALUE returns blank or the alternate result, MAX returns the highest rate, AVERAGE returns a blended rate. None of them is the sum of the converted rows.

A second variant wraps the rate in a helper measure that returns a text message ('Select one currency') when several are in context. Any arithmetic on that measure is then a type error, and dependent measures show blank.

Convert at the granularity where the rate is unique, then sum. Iterate over the distinct combinations of currency and period (not over the fact table), fetch the rate once per combination, multiply the sales of that combination, and add up.

This is also the fast version. The common alternative, ADDCOLUMNS over the whole fact table with a FILTER on the rate table inside, computes the rate lookup once per fact row and turns a 2 ms measure into a 20 s one on 10 million rows.

Statically: iterators that also carry FILTER or CALCULATE over the fact, arithmetic on a measure that returns text, and measures whose return type varies by branch. The AI review specifically tests every conversion measure for the total-row case, because that is what an expert would try first.