'Simple MDX Calculated Member

In my simple cube, I have a measure = \[Measure\].\[Salary\], I have also \[DimEmpployee\].\[EmployeeLastName\].\[Smith\]. I would like to create calculated measure, where I can display in Axis 0 two measures - \[Measure\].\[Salary\] and calculated measure \[Measure\].\[SmithsSalaries\], to compare difference between Smith's earnings vs Total Salary.

I would like to compare Measure.SmithSalaries with other measures accross all diemensions. Is it possible to create such a measure using SCOPE statement?

I was playing around SCOPE statements, but it was displaying results only if DimEmployee was selected. I am looking for something which is running in blocks to avoid performance issues.

mdx


Solution 1:[1]

I think you only need a simple calculated measure.

CREATE MEMBER CURRENTCUBE.[Measures].[SmithSalaries]
 AS ([DimEmployee].[EmployeeLastName].[Smith], [Measures].[Salary]), 
VISIBLE = 1  ;

After that you can combine that with you total salary for example to get a ratio.

CREATE MEMBER CURRENTCUBE.[Measures].[SmithSalaries Ratio]
 AS DIVIDE(([DimEmployee].[EmployeeLastName].[Smith], [Measures].[Salary]),[Measures].[Salary]) 
VISIBLE = 1  ;

SCOPE allows you to have different behaviors when different combinations of Dimensions are into play, like returning a different calculation when the DimEmployee is selected but otherwise just return the normal calculation. Like a Very efficient IF condition to check what are in the Axis of this calculation.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 mxix

Related Questions