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