Ratio to all & Ratio to parent within a list

PrevContributor

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

peter_mcanena

Ratio to All is easy: the formula is

Sales / Sales[SELECT: Product.All Products]

(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 

Sales / Sales[LOOKUP: Product Subgroup.Product Group]

Then your Product Group-level formula would say 

Sales / Sales[SELECT: Product Group.All Products]

This is just one of many reasons why multi-level lists are not as good as using a separate list for each level of your hierarchy.

PrevContributor

Thanks peter, I was afraid of that. I was hoping for something a bit more dynamic!

I'll give it a shot

harish_bk

Hi Jakub

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.

peter_mcanena

Hi Hamish,

That sounds like it should work, but if you try it you'll find the formula only evaluates at the bottom level of the tree, so it won't work for upper members.

Pete