Get Value of Top Level


I have three lists: Product Lines L1, Product Category L2, Products L3. Total Products is the top level of Product Lines L1. I'm trying to calculate the product mix of each product in Products L3 as a percentage of Total Products. The module.line item that has the volume is Sales.Units Sold. But my question isn't about the mix calculation. I'm first concerned with just trying to get the Total Products value from the Sales module. I've tried a number of different formulas, but nothing seems to work. So how do I get the Units Sold value at the top level?

Best Answer


  • Thank you!
  • Hi

    You can use the hierarchical capability of SELECT() function to roll up any hierarchical attribute and get the desired value. In this case, you can roll up the total items sold at the top level.
  • Just to add to this, you may have to find where 'Total Company' sits on your Lists Hierarchy, so for me the formula was:


    Unit Sold[SELECT: Country.Total Company]


    Where 'Country' is the name of my list that contains 'Total Company' (see snapshot)

  • Kindly note, when you just refer a line item from a source module into a destination module, in such a way that the destination module does not have the source dimensions, it will automatically pick the top level for each of the source dimension. No need to specifically mention select - top level.