Summary Method - Multiply/Product

sd12901
New Contributor

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 

13 REPLIES 13
Misbah
Moderator

Re: Summary Method - Multiply/Product

@sd12901 

 

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

sd12901
New Contributor

Re: Summary Method - Multiply/Product

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
Certified Master Anaplanner

Re: Summary Method - Multiply/Product

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. 

dsoomar002_0-1615473666495.png

 

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

 

dsoomar002_1-1615473997402.png

 

 

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
Certified Master Anaplanner

Re: Summary Method - Multiply/Product

@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. 

dsoomar002_0-1615474493616.png

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

 

Formula: Percentages * Percentages * Percentages

dsoomar002_1-1615474568568.png

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

Formula: 'TEST03: Percentages'.Average.

 

dsoomar002_2-1615474724200.png

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
New Contributor

Re: Summary Method - Multiply/Product

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
Certified Master Anaplanner

Re: Summary Method - Multiply/Product

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. 

 

dsoomar002_0-1615477338688.png

 

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

Formula: "1". 

 

dsoomar002_1-1615477431271.png

 

 

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

 

dsoomar002_0-1615477696642.png

 

 

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
Regular Contributor

Re: Summary Method - Multiply/Product

@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
Certified Master Anaplanner

Re: Summary Method - Multiply/Product

@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
Regular Contributor

Re: Summary Method - Multiply/Product

@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

CommunityMember126793_0-1615555551859.png  CommunityMember126793_1-1615555604622.png

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

CommunityMember126793_2-1615555660698.png

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

CommunityMember126793_3-1615555691710.png

2nd scenario:

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

CommunityMember126793_4-1615555934883.pngCommunityMember126793_5-1615555960303.png

CommunityMember126793_6-1615555992720.png

 

Strange na!!

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