Your issue as that as soon as look at the Region level, you have multiple exchange rates for the Region. Because there are multiple values, your attr([Sheet1 (FRHI Hotel Listing 2013.xlsx)].[Curr Rate]) in your [US Dollars] calc is going to evaluate to *, which you cannot multiply by the sum([Local Currency]). Remember that in blending, measures are aggregated before they are joined. You therefore lose the necessary granularity to perform your calculation when you jump up a level.
My suggestion would be to convert to US Dollars before bringing the data into Tableau, but I understand this might not be an option.
If you de-select "Show Header" for the "Store" field on the Rows shelf, it will still show the total, but the label for "total" will not appear. I imagine this isn't what you want, but other methods will likely be more complicated.
It should be noted that you currently are NOT seeing the total of US Dollars for all regions. The total you do see is for the Local Currency column only, as US dollars is coming from the secondary source and I do not believe subtotals work for blended aggregations.
Thank you Zac. I did not know those limitations in using two data sources..
Yeah your suggestion would be my last resort.. Thanks.
Thanks Matt. Appreciate your answer.