CODEWITHSUNDEEP
ssasmdx
user3063530
user3063530

Reputation: 89

MDX Query to create a calculated measure

My requirement:
Calculate the Gross Margin Amount which is difference of PL Amount for Revenue and Cost of Sales.

Revenue and Cost of Sales belong to a PL Category dimension and PL Amount is the measure.

To calculate the difference, I have written the following. How do I proceed further?

CREATE MEMBER CURRENT CUBE [Measures].[Gross Margin Amount] AS 
(
([Profit and Loss].[Profit and Loss].[Category].&[100],[Measures].[PL Amount]) - 
([Profit and Loss].[Profit and Loss].[Category].&[200],[Measures].[PL Amount])
)
FORMAT_STRING = "Standard"
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Measure';",

Upvotes: 1

Views: 1355

Answers (1)

whytheq
whytheq

Reputation: 35557

I'm not a cube designer so a little unsure. Does adding in the AGGREGATE function help:

CREATE MEMBER CURRENT CUBE [Measures].[Gross Margin Amount] AS 
(
AGGREGATE([Profit and Loss].[Profit and Loss].[Category].&[100],[Measures].[PL Amount]) - 
AGGREGATE([Profit and Loss].[Profit and Loss].[Category].&[200],[Measures].[PL Amount])
),
FORMAT_STRING = "Standard"
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Measure';,

Upvotes: 2

Related Questions