Calculation for base level entity, Roll up at parent Level

New Contributor

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!







Two rough suggestions:

  • define a Dynamic Calc account for this, which can do different calculations depending on whether you're looking at base or parent members
  • Split the view into multiple rows, one for each parent followed by one for the children. Then, in the parent row, use Sum of rows to calculate a sum of the children rows. This only works if those values are already usd though.