Calculation for base level entity, Roll up at parent Level
We have a cube view with Entity Hierarchy in rows displaying values in local currency at the base level and USD at parent level.
columns are CY Amount, PY Amount, Variance, Variance %, Currency Change
Note - Currency Change is always reflected in USD(both at base and Parent Level)
Formula for currency change = (CY Avg Rate - PY Average Rate)* CY Amount (conversion to USD)
Problem Statement
Currency Change is displaying correct values at the base level entity, but when rolling up its showing a 0 because the CY Amount at Parent level always shows in USD so the conversion rate by default is 1.
I am looking for a solution to have the Currency Change apply only at the base level and the parent should be a rollup of the base. see screenshot below. Looking forward to some inputs here!