Custom Sum without Introducing New Dimensions

Highlighted
Regular Contributor

Custom Sum without Introducing New Dimensions

When you perform a custom sum (either because you've switched off the line item's sums or because you're calculating a partial sum or using a different summary method), the usual method is to define a list called, e.g. Include, containing just one item, say Include.Include, and then add that list to the dimensionality of the target line item (i.e. the one you're summing into). This allows you to say

Source Line Item[SUM: Include in Sum]

(where Include in Sum is a line item or a list property formatted to the Include list) But it means that you've got an unwanted dimension in your target line item. You can get round this by adding another line item without the Include dimension and saying

Target Line Item[SELECT: Include.Include]

but that uses twice as many cells as you want. And you can't combined the steps by saying [SUM: Include Sum, SELECT: Include.Include] because you can't combine SUM and SELECT in this way. The alternative is to use LOOKUP instead of SELECT. Define a unitary (dimensionless) line item somewhere in your model, called e.g. Include Summary.Include, with the formula

Include.Include

then you can use it to do your summaries in one step:

Source Line Item[SUM: Include in Sum, LOOKUP: Include Summary.Include]

You might expect this to be equivalent to Source Line Item[SUM: Include in Sum, SELECT: Include.Include], but in fact it behaves differently. You get to define your sums the way you want without introducing any intermediate line items or extraneous dimensions. NB: Anaplan advise caution when combining SUM and LOOKUP. Apparently it can lead to performance issues under certain circumstances. But I use this all the time and I haven't encountered any problems. I've even had the model profiled to identify performance bottlenecks and these formulae have received the all clear.