I have setup up a multi-dimensional data model in SSAS which is embedded in Microsoft Excel as a Pivot Table. In Excel I want to use the OLAP what-If analysis functionalities.
Screenshort Excel functionality
My understanding is that every change in the Pivot Table Triggers and UPDATE CUBE statement. However, I would like to change the default allocation expression to a custom allocation expression, for which Excel nicely offers an interface:
Screenshot Excel allocation expression
The Microsoft MDX Documentation (https://learn.microsoft.com/en-us/sql/mdx/mdx-data-manipulation-update-cube?view=sql-server-ver16) states that the standard in standard the following expression is used:
Weight_Expression = <leaf cell value> / <existing value>
I want to build up a custom allocation method. However, already starting with this simple default example I run into the issue that I do not know / could not find a generic operator for e.g., '<existing cell value>' or '<leaf cell value>'. But without it this would mean that I always have to name my specific measures in the code, which in turn would lead to a complicated code for all different kind of values in my table in excel.
Thus my question is: Are there generic expression to get the existing value of the cell and leaf cell to use in the weight expression? And if not how is the default allocation expression actually scripted in MDX?
Thanks for your help.
I already tried using the default code however when using<leaf cell value> / <existing value> I receive a syntax error.