I have a fact table containing daily inventory transactions, so I have to add the cumulative records to get the values for a given date. This is complicated by having transactions from various global divisions with monetary values in the native currencies. I have USD equivalents of all monetary values by multiplying the cumulative native values by the respective currency rate on the given date. This is the syntax for the TotalValueUSD calculated measure:
sum(PeriodsToDate([Calendar].[Yr Mnth Day].[(All)]),[Measures].[TransTotalValue]) * [Measures].[CurrExchRate].[All]
Everything displays perfectly in a pivot table, except for the row totals. It appears to be summing the various exchange rates and then multiplying that by the sum of the total transaction value, which totally distorts the result. Below is an example with the U.S. and Taiwan divisions selected:
Would someone tell me how the proper formula/syntax for the TotalValueUSD measure should be so that the proper value appears in the row total? Thanks.