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. Alternatively, in the Measure member selector, you can right click on the measure or use the "Calculated Value..." drop down option.

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 appropriate level. (e.g. the year level for Previous Year calculations)

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.

Measure to Base

This shows the current value as a percentage or the value at a higher 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.

Rank position

This shows the rank of current row compared to the other values.

You can rank compared to

• Axis - Ranked against the other rows in the grid
• Level - Ranked against other members at the same level
• Siblings - Ranked against other members with the same parent

This example shows the sales ranking instead of the actual value. You could select both Value and Rank on columns is you wanted to.

DAX

If you are connected to a tabular data source, the DAX calculations tab will also be available. Read more here.