none
SSAS Calculation Question

    Question

  • Hi,I have the following question regarding a calculation in SSAS. I have the following mdx in my cube:

    CREATE MEMBER CURRENTCUBE.[Base VWAP Calc]
     AS
        ([Object].[Channel].&[Sales Margin], [Object].[Sub Measure].&[Revenue], [Measures].[Revenue $])
        /
        ([Object].[Channel].&[Sales Margin], [Object].[Sub Measure].&[Volume], [Measures].[Volume MWh]),
    FORMAT_STRING = "#,##0.00;-#,##0.00",
    VISIBLE = 0;

    Essentially this calculation is dividing the Revenue by Volume to get the BASE VWAP. This calculation only applies to the Object Channel of Sales Margin, but the issue I'm getting is that the value is repeating in all of the other object channel values so I'm wondering if there is a way to restrict this calculation to only show for Sales Margin.Many thanks

    Monday, October 14, 2013 9:07 PM

Answers

  • You'd need to create the member as a NULL and then scope the calculation to just the [Sales Margin] member.

    CREATE MEMBER CURRENTCUBE.[Measures].[Base VWAP Calc] AS NULL, VISIBLE=0;
    
    SCOPE([Object].[Channel].&[Sales Margin], [Measures].[Base VWAP Calc]);
    	THIS = (
    		([Object].[Sub Measure].&[Revenue], [Measures].[Revenue $]) 
    		/ 
    		([Object].[Sub Measure].&[Volume], [Measures].[Volume MWh])
    	);
    	
    	FORMAT_STRING(THIS) = "#,##0.00;-#,##0.00";
    END SCOPE;
    
    HTH, Martin

    <a href="http://martinsbiblog.spaces.live.com" target="_blank">http://martinmason.wordpress.com</a>

    Tuesday, October 15, 2013 2:41 AM