Summary Method - Multiply/Product

Hi all,

I currently have one line item with 2 dimensions, and the numbers are percentages. I essentially want to strip out one of the dimensions in a new line item but to have all the percentages multiply by each other for the dimension that was taken out.

In simple terms, I'm looking for a 'Summary Method' that multiplies rather than a sum / formula / average etc.

Does anyone know how this can be done? I've attached an Excel which shows what I want to get to in the line item. Apologies if it's something completely obvious. 🙂

Many thanks

Summary_-_Multiply_100846.xlsx

Accepted answers

All comments

Misbah

@sd12901

Are the percentages in your current View/Table calculated or input? And How are you calculating the percentages in your Result Table?

sd12901

Hi Misbah,

The percentages in my current view are all calculated with a formula from other different inputs. The result table formula is the issue I'm having as I'm not sure how to multiply the percentages with the other percentages from the other items in the dimension.

If there was a 'Multiply' option as a Summary Method for example, that would solve my issue.

Thanks

DaanishSoomar

Hello @sd12901 @Misbah,

I have a solution that is a bit of a workaround. This workaround entails 3 steps.

1. I created the module which has percentagtes dimensionalized by Dimension 1 x Dimension 2.

Step 1.PNG

2. I created a "Constants" module, that has no dimensions, only line items. The line items are list formatted as Dimension 2.

3. I created a module dimensionalized by Dimension 1, line item called "Average Percentages".

Formula: 'TEST03: Percentages'.Percentages[LOOKUP: 'SYS00 Constants'.Green] * 'TEST03: Percentages'.Percentages[LOOKUP: 'SYS00 Constants'.Blue] * 'TEST03: Percentages'.Percentages[LOOKUP: 'SYS00 Constants'.Red]

Considerations:

-This method requires the maintanence of a Constants module. My initial idea was to use SELECT statements but this goes against the Planual principle avoid hardcoding formulas. So instead used a Constants module and performed the multiplication manually.

Let me know if this helps!

I am going to keep playing around with the summary methods and different logic/line items to see if there is a way to do this without using a Constants module.

DaanishSoomar

@sd12901 @Misbah,

I do have another method for you. In addition to the above method you can try this:

1. 1. I created the module which has percentages dimensionalized by Dimension 1 x Dimension 2. Summary Method: Average.

2. I have a 2nd line item in the same module, called "Average". Summary Method: Formula.

Formula: Percentages * Percentages * Percentages

3. I have another module dimensionalized by Dimension 1. Line Item: Avg. Percentage.

Formula: 'TEST03: Percentages'.Average.

Considerations:

This method does not require the manual maintanence of a Constants Module. The multiplication of 3 percentages in Step 2, is something that would have to be maintained as when you have to add more colors I believe.

Hopefully this helps!

sd12901

Hi @DaanishSoomar,

Thanks a lot for the detailed explanation and for taking the time to investigate! I do have a lot more items in each dimension than my initial example so I may look to explore a way to not have a long formula.

If I can't find anything, I may use this example as the list items are unlikely to change regularly and it could just be a one-off long formula to begin with.

Much appreciated.

DaanishSoomar

Hi @sd12901, @Misbah,

No problem! I made 1 last attempt at the formula, to see if there is a way to automate this without any maintanence. I think I found something you will like!

1. I made a module dimensionalized by Dimension 1 x Dimension 2. Line Item: Percentages. Summary Method: Average.

2. I made a System module dimensionalized by Dimension 2. Line Item: Count. Summary Method: Sum.

Formula: "1".

3. I created a module dimensionalized by Dimension 1. Line Items: Avg Percentage. Summary: none.

Formula: POWER('TEST03: Percentages'.Percentages, 'TEST05: Dimension 2'.Count)

I gave some thought to some of my previous responses and noticed that I am essentially multiplying "average percentage" by itself "X" number of times. Where X = the count of Colors.

Well that sparked my curiosity, because multiplying something by itself is essentially taking a number and raising it to an exponent. So I had average percentage, I just needed the count of colors, which I achieved through the system module.

Alas, the formula thus is taking "Avg Percentage" ^ Count of Colors.

Let me know if this works for you!

CommunityMember126793

@DaanishSoomar @sd12901 very interesting puzzle to solve.. keep me in the post if u find the solution..

in the meantime, am also working and looking for solution

DaanishSoomar

@CommunityMember126793 @sd12901 ,

I believe the 3rd option which I presented using the formula with power may be the cleanest route. Let me know if you can test this and confirm. @CommunityMember126793 - open to other ideas as well.

CommunityMember126793

@DaanishSoomar @sd12901

Let me present 2 scenarios, 1 way it passed and another way it failed..... writing during my findings .....

1st scenario:

1. SUMMARY as average (Anaplan result) Results from Excel

2. Module 2... Line Item (COUNT) as 1

3. Final Module with Result.. All tallied except Shirts....(compare with the excel shown in 1st point)

2nd scenario:

Once I have changed the figure to 90% against Chennai in Input cell.. means Chennai + Shirts cell ...... all tallied..

Strange na!!

@DaanishSoomar I hope have kept all modules and settings intact as per 3rd solution.. Share your thoughts

rob_marshall

@sd12901

Ok, I have a solution for you, check this out and see if this gets you home...

Here is your data:

Create two line items:

LN: LN(Data)

Final: ROUND(EXP(LN), 2, NEAREST, EXACT)

See if that works for you.

Rob

DaanishSoomar

Hey @rob_marshall,

Very cool solution! I would be curious to see your thoughts on the difference from a PLANS standpoint in utilziing the POWER function from my post earlier as compared to the use of ROUND/LN functions in your example here.

Is 1 better than the other from a performance, processing, and sustainability standpoint?

rob_marshall

@DaanishSoomar

From a PLANS standpoint, not really. The only real difference is I am doing all of the calcs within the same module (two of them) and not having to use a system module. Both methods appear to work, so I would go with what is the most comfortable/logical to your way of thinking.

Rob

DaanishSoomar

Got it! Appreciate your insight @rob_marshall! I like your alternative approach as well 🙂

Quick Links

Can you help?

Unanswered questions

Not Able to Join the Level 1 Model Building Course
Hey Everyone, I was trying to learn about the Level 1 Model Building Course, when i log in to my system and click on "Start the course" option its showing training unavailable, Can anyone please help me with this @Dikshant and all?
Generating Failure Dump - even though no warnings/error in Actions
I've few actions in our Supply model which when run takes some time on the "generating failure dump" step at the end of the action even though the import is a full success without warning or errors. This is just a few seconds when run for a single action, however we have a process with 120 actions, and most of these…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…