MDX formula wizard

XLCubed has allowed custom calculations for some time, and in Version 9.1 there is now some extra help building your formulae.

You can access the wizard using the wand toolbar in the Custom calculations form, or the "Calculated Value..." drop down option in the member selector for measures.

Calculation Types

Time

There are various time calculations available, such as

• Totals to Date
  • Year to Date (YTD)
  • Quarter to Date (QTD)
  • Month to Date (MTD)
• Running totals
  • Average, Sum, Total To date
  • Running total in Report is only available in the context of a particular report, so must be accessed from the member selector.
• Opening and Closing values
  • Including finding first/last non-blank values
• Comparisons to previous periods
  • Actual and differences

If the cube is configured with Year/Quarter/Month levels defined correctly then XLCubed can find the correct levels for many of these calculations. If not you will be asked to pick the appopriate level.

Slice

This shows the current measure, but for a different slice of the cube.

For example you could specify that the new measure always shows the sales of Red products only rather than the full total.

Compare to Base

This shows the current value as a percentage or the value at a highe level.

For example if you picked Geography at the Country level, each state or city would show the value it makes up of the country it is in.