Hi all, I have a module with a single multi-level list (category, subcategory, product). The 'all' level is called 'All Products' I need 'weight to parent' and a 'weight to all' line item formulas to use in my calculations. It's coming unstuck when i try to look up the 'denominator' for the weight calc. eg the value of the current item's parent, or the list total. It looks like i can't use item and lookup because the items reside in the same list the MDX syntax would be something like divide(measurename,(measurename,listname.currentmember.parent)) for the weight to parent & divide(measurename,(measurename,listname.all products)) for the weight to all is there an anaplan alternative? is there a different way i need to model my data to be able to retrieve the value of the current item's parent or the list total? Cheers
(I'm assuming your list is called Product and the measure you're working with is called Sales.)
However, for Ratio to Parent, you won't be able to do it with a multi-level list. There are two things stopping you: Firstly, there's a bug (or what looks to me like a bug) that stops LOOKUP working properly when you're looking up one level in a list to another, which I think is the problem you've found yourself. You could get round this by creating a duplicate product list and using that as an intermediate stage in getting the denominator.
But if you did that you'd soon encounter the second problem, which can't be solved so easily: Whatever formula you come up with will only calculate on the leaf-level items in your Product list. All levels above this need to be derived using an aggregation function (Sum, Formula, Min, Max etc.) and there isn't an aggregation function that will recalculate the ratio at every level.
So the only solution is to replace your multi-level list with a rigid hierarchy, with a separate list for each level: say Product Group, Product Subgroup, Product. You can then write a separate formula for each level.
Your Product-level formula would say
Sales / Sales[LOOKUP: Product.Product Subgroup]
Then your Product Subgroup-level formula would say
1) Create a property with formula
2) Let the formula be written to derive the parent of the list item
3) For parent members use formula source/Sum(Select the property) similar to sumif
4) For total you already have a formula
I have not tried this but seems it should be dynamic.