Help Replicating Excel Formula

Hi,

We are trying to replicate the following set up and formulas from the below Excel snap shot into a Module. Currently the Module is set up where there are two list in the rows. In the Excel the formula for the Rollup is based on a calculation and we want to replicate that in Anaplan, but in Anaplan the Parent item automatically sums the children in the List. (attached is the Excel with the formulas in question highlighted in yellow).

Below is a screen shot of the Module we currently are using to try and create the Excel version.

Anaplan_Community_question_118552.xlsx

Accepted answers

All comments

KirillKuznetsov

@neg177check out Ratio summary method:https://help.anaplan.com/9ab4e05a-1748-4027-****-525d5bdd5b22-Summary-Method#Ratio

To achive this in your case it can be used the following way: You have to create (in the same module) a separate line item with your calculation for Rollup and one more line item (number formatted) "1 as denominator" with no dimensions an a formula =1.

In ratio settings you have to put the Rollup formula in numerator and "1 as denominator" in denominator.

This way your rollup calc will be devided by one. Which means - stays as is.

Cheers,

Kirill Kuznetsov

Advanced Consultancy

Amaya

Hi @neg177 ,

I would understand that you want to show 'weighted average' on the summary item.

You already have had the weights, so you multiple the targets number by them.

And sum the results and divide it by the sum of the weights.
Let's use the ratio summary method as @KirillKuznetsov wrote.

In this situation, the sum of weight equals 1 (A below), but it is needless even to calculate weights, and you can use the amounts directly instead of the weights (B).

Weighted Average2.png

I hope this helps.

Taichi

Quick Links

Can you help?

Unanswered questions

How have you from-scratch built lot-level, geo-based Inventory Consumption?
We’re working on modeling inventory consumption at the lot level, accounting for shelf life considerations, since we need visibility into which lots may not consume—both for risk reporting and E&O analysis. Each item may have customers with different shelf life requirements. For example, item A may need 270 days for some…
How to bring Row data into Columns?
Hi, Can you help me, how can we get row data into columns using Unique ID? Per attached below first snapshot, I want to bring rountingOrder 4, RecipientName, Sent and Completed dates in 1 row as show in sanpshot 2. Do I need to create Hierarchy list and Map?
How to Design Simulation Planning in Anaplan with Multi-Level User Input and Step-by-Step Logging?
Hi Anaplan Community, I'm working on a simulation planning model where users need the flexibility to input pricing adjustments at any level of the product hierarchy, and I’d love your guidance on best practices. 📌 Scenario Overview: We have a 4-level product hierarchy: Brand Category Sub Category SKU (lowest level) The…