Hello! I am new to Prophix so this may be a very basic question, however, I cannot figure it out. I am wanting to calculate an Average Sales Price for Product A as a stat account that is an input from Region 1 price and Region 2 price. However, it may not always be only Region 1 and Region 2. There could be additional regions (too many to create an input stat account for each one) to include in the average, or, it could only be one region. So, using a hard-coded divisor in a formula wouldn’t suffice. The other issue is, when I view this by each Region (leaf), which rolls into Division, I want to see the average price at each entity level (Region and Division), rather than a sum at the Division level. Any help would be greatly appreciated.
I think you can use Calculation method as Average, there are two options Including and Excluding(Empty Values), if you only want Sales Price Average.
This should give you simple average, however if you want to divide it by some other account, than it would make it complex and may require a MDX formula.
Regards,
Ali Arsalan
1 Like
Hi Ali,
Thank you for the response; however, that only affects the time conversion (period). I actually need an average at the non-leaf level (Region > Division). I tried the Non-leaf formula option to average the two, but it didn’t seem to work. I think you may be right in that I need a MDX formula, but I’m not familiar with how those work (yet 
)
1 Like
Is your Region / Division a Dimension of Cube?
Did you tried Average Excluding Empty option as it will only consider the region/division which will have value while average using Standard Formula.
Regards,
Ali Arsalan
Hi Ashley,
Since you have CSP, you can work with our Product Adoption team to have this resolved . Your Customer Success Manager has submitted a ticket on your behalf and our team would be able to help you with an MDX formula on a call.