We have a requirement to show reports in constant currency and USD.
In the Financials - AP Payments subject area, I have added exchange rates on the Currency dimension.
My calculation is
Payments Amount CD: SUM( Payments Amount * Exchange Rate BY Ledger Currency )
I'm using Payments Amount from Facts - Ledger Currency and Ledger Currency from Currency dim.
- This works correctly on tile and table visualizations only when I edit the properties of each visual and set the Aggregation Method to Sum and the By to Ledger Currency.
- However, it doesn't work if these properties are not set on the visual.
- Instead, it generates a list of the total sum multiplied by each rate, and for the total shows the first value in the list.
- If I wrap a sum around the formula, I get a sum of these three values, which is incorrect.
Is there a formula method to get the same results as setting Aggregation Method to Sum and BY to Ledger Currency on the visual?
