Search code examples
excelssasmdx

Calculated measure in Excel cube functions

How I can use calculated measures in Excel Cube functions?

I would like to creates simple measure in Cube function and use it in CUBEVALUE, so I look for something like this:

CUBEVALUE("SSAS" "SUM( {NULL:[Calender].[Day].CURRENTMEMBER.LAG(8)}, [Measure].[x])";other dimensions")

I cannot create this measure in cube (this is solution for end users).

Solution

  • The CUBEVALUE function expects a member, not a formula that returns a number. So you need to create the calculated member in the cube or at least on the session. If you don't want to alter the definition of the cube and prefer the calculation lives in the Excel workbook's session with the cube, then do the following.

    Install the free OLAP PivotTable Extensions. Create a PivotTable somewhere in your workbook against that "SSAS" connection, right click on the PivotTable and choose OLAP PivotTable Extensions. Create a new calculated measure with name My Calc and formula:

    SUM( {NULL:[Calender].[Day].CURRENTMEMBER.LAG(8)}, [Measure].[x])

    Then change your CUBEVALUE formula to:

    =CUBEVALUE("SSAS","[Measures].[My Calc]")